Create columns from rows in SQL Server

sql, sql-server, sql-server-2008

Solution

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.

Problem

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.

Original source