Geek asks and answers

Creating columns from rows in SQL Server

I have an SQL query that returns data in the following 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 summarize the total hours and bring the result, like:

2012   2011  2010
 600    225   175

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

+3
source share
5 answers

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

SQL SERVER Examples - PIVOT and UNPIVOT

+3
source

You can do this using PIVOT, or Static PIVOT, where you hard-code the years, or Dynamic PIVOT, where you create a list of years when you run 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 an SQL script with an example

Dynamic rotation:

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 get the same results.

+3

. , , , , , :

 select sum(p.TotalHours), p.Year From someTable p
    GROUP BY p.Year

b. you have to do some logic to create columns in rows and vs.

0
source

You can use sql grouping http://www.w3schools.com/sql/sql_groupby.asp

select 
    [Year], 
    SUM([Hours]) as HoursByYear
from 
    #table  
group by 
    [Year]

result:

    Year    HoursByYear
    2010    175
    2011    225
    2012    600

or pivot , for example, Pranay Rana suggested

select
    [2010], [2011], [2012]
from
    (select [YEAR], [Hours]
        from #table) AS SourceTable
    pivot
    (
        sum([Hours])
        for [Year] IN ([2010], [2011], [2012])
    ) as PivotTable

result:

2010    2011    2012
175     225     600
0
source 
select sum("Total Hours") as "Total Hours", Year 
from tablename 
group by Year

This simple query will give you the desired result.

-1
source

More articles:


All Articles