SQl Server 2005: Rows to columns -- how to do this challenge?

Question

Please let me know, How to convert the following data ,

[id]  cost1  cost2    year 
   1      5     10    2010 
   1      4     15    2011 
   2     10     10    2010

into this format [rows of 'Year' to columns heading]

id [cost1-2010] [cost2-2010] [cost1-2011] [cost2-2011] 
 1           5           10            4           15 
 2          10           10            0            0

Answer 1

Use PIVOT

example: http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

Answer 2

If you want to do this dynamically based on data, it is going to be more difficult than just using PIVOT. For PIVOT or the conditional sum technique

[2010Values] = ( SUM(Case when year = 2010 then FieldValue Else 0 End)

you must know the column name ahead of time.

If you wish to set column names dynamically based on the data received then you'll have to go the dynamic SQL route which can get ugly.

Answer 3

Check out the discussion on this post. That should have you dialed.

Answer 4

try something like this:

DECLARE @YourTable table (id int, cost1 int, cost2 int, year int)
INSERT @YourTable VALUES (1,5,10,2010)
INSERT @YourTable VALUES (1,4,15,2011)
INSERT @YourTable VALUES (2,10,10,2010)

SELECT
    id
        ,SUM(CASE WHEN year=2010 THEN cost1 else 0 END) AS "Cost1-2010"
        ,SUM(CASE WHEN year=2010 THEN cost2 else 0 END) AS "Cost2-2010"
        ,SUM(CASE WHEN year=2011 THEN cost1 else 0 END) AS "Cost1-2011"
        ,SUM(CASE WHEN year=2011 THEN cost2 else 0 END) AS "Cost2-2010"
    FROM @YourTable
    GROUP BY id

OUTPUT

id          Cost1-2010  Cost2-2010  Cost1-2011  Cost2-2010
----------- ----------- ----------- ----------- -----------
1           5           10          4           15
2           10          10          0           0

(2 row(s) affected)