arun.sas (9/28/2009)

---

select id, item, f_year,

sum(case when f_month = 01 then qty else 0 end)/sum(qty)*100 as 'P1',

...

Ok, I have had a chance to test out your solutions and am glad to say that after a bit of fiddling it is returning the data I need! We...

Thanks all, I'm out of the office so I'll test your solutions tomorrow.

Just to clarify, what I wanted was to first order the rows in the temp table by number...

Allister Reid (6/9/2009)

---

No bother, btw I really like this solution for finding the median, pretty neat. Will have a play around with large result sets when I get a...

Perfect! Thanks Allister, that works.

To find the median of a result set you have to first order the result set and then depending on the number of items in the set either return the...

Sorry, here's the full code:

use tempdb

go

create table Test (Product nchar(20), Actual_Rate decimal(14,2), Quantity int)

insert into dbo.Test

select 'A-1015-7658',35.95,1

union all

select 'A-1015-7658',35.95,1

union all

select 'A-1015-7658',120.19,3

union all

select 'A-1015-7658',80.13,2

union all

select 'A-1015-7658',45.39,1

union all

select 'A-1015-7658',45.39,1

;with MedianTest as

(

select Product,

...

Flo, I managed to do it by following Bob's method!...

Linda hammell (4/6/2009)

---

Just wanted to let you know that after a bit of fighting with it, I was successful in using a UDF to put the data in the format...

All,

Thanks for your input, I'll test out all of the suggestions and get back to you but it may take a little time!

Hi Ramesh,

No, my XML Source is reading data from an Excel file that is in XML format and there are values from the Excel output that I want to store...

Hi Ramesh,

Slightly different question this time. Using the following:

CREATE TABLE [dbo].[Test](

[name] [varchar](10) NOT NULL,

[jan] [int] NOT NULL,

[feb] [int] NOT NULL,

[mar] [int] NOT NULL,

[year] [int] NOT NULL)

INSERT INTO [dbo].[Test] (name, jan,...

Thanks for your help Ramesh

Thanks Guys but I'm still stuck! Here's a bit more info, firstly I've created a view that PIVOTs the data:

CREATE VIEW PivotData

AS

SELECT id, description, [JAN], [FEB], [MAR], header

FROM dbo.ForecastPartsD

PIVOT (SUM(units)...

Thanks both for the quick response, your answers were exactly what I was looking for!