Create columns from rows in SQL Server

Question

I have a SQL Query that bring data in the below format;

Total Hours   Year   
  100.00      2012 
  200.00      2012 
  300.00      2012 
   75.00      2011 
  150.00      2011 
   50.00      2010 
  125.00      2010 

I need to sum the total hours and bring the result set as;

2012   2011  2010
 600    225   175

Please help me here!. Let me know if you need more information.

Answer 1

You can perform this with a PIVOT, either a Static PIVOT where you hard code the years or a Dynamic PIVOT where you create the list of years when running the query:

Static PIVOT:

create table table1
(
  totalhours decimal(10, 2),
  year int
)

insert into table1 values(100, 2012)
insert into table1 values(200, 2012)
insert into table1 values(300, 2012)
insert into table1 values(75, 2011)
insert into table1 values(150, 2011)
insert into table1 values(50, 2010)
insert into table1 values(125, 2010)

select *
from
(
  select *
  from table1
) x
pivot
(
  sum(totalhours)
  for year in ([2012], [2011], [2010])
) p

Here is a SQL Fiddle with an example

Dynamic Pivot:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.year) 
            FROM table1 c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ' + @cols + ' from 
            (
                select totalhours, year
                from table1
           ) x
            pivot 
            (
                 sum(totalhours)
                for year in (' + @cols + ')
            ) p '


execute(@query)

Both will give you the same results.

Answer 2

Do red this on msdn Using PIVOT and UNPIVOT this will resolve the isssue easily..

SQL SERVER – PIVOT and UNPIVOT Table Examples