[RESOLVED] Query output Prob

[Bill Crawley]

Hi ALL,

Using SQLServer 2005 I have a table made up of the following format:

Id = primary key
Type = Primary key
Year = Primary Key
Qty



If I do a simple select say Select * from mytable I get the expected output of data in the following format.

512 a 1 300
512 a 2 220 etc

I want to turn the data on it's side so that I get it in the following format Effectively Grouped by id and type

Id Type Year1 Year2
512 a 300 220

Any ideas how I get an output like this without using a cursor and placing in temp table first? I was wondering if there is a single Query that would do the trick.

[Shuja Ali]

How about using a query similar to this
Select
A.ID, A.TYPE,
max(case when A.YEAR = 1 then A.QTY end) as YEAR1,
max(case when A.YEAR = 2 then A.QTY end) as YEAR2,
max(case when A.YEAR = 3 then A.QTY end) as YEAR3
From
(Select
A.ID, A.Type, A.YEAR, A.QTY
From
TEST A Join TEST B
On A.ID = B.ID and A.Type = B.Type
Group By
A.ID, A.Type, A.YEAR, A.QTY) A
Group By
A.ID, A.TYPE

[Bill Crawley]

Thanks Shuja,

THis is perfect.