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.
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.
Do red this on msdn Using PIVOT and UNPIVOT this will resolve the isssue easily..
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, like 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
a. you maybe getting data somehow like this, but do like this maybe it'll help:
select sum(p.TotalHours), p.Year From someTable p
GROUP BY p.Year
b. you should do some logic to make columns into rows and vs.
select sum("Total Hours") as "Total Hours", Year
from tablename
group by Year
This simple query will give you the desired output.
