Pivot Table Help needed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Pivot Table Help needed

Author

Topic

AbuGaarith
Starting Member

2 Posts

Posted - 2011-06-29 : 04:39:40

Hi
Could some1 pls assist.
My data is looks like this

Year Age OpenBal IntroBal DistInc Adjust
2010 12 1100 1000 0
2011 13 901 0 0
2011 13 834 0 1600 0
2012 14 700 0 1700 0
2013 15 521 0 1800 0
2014 16 365 0 1900 0

But I need this

Year 2010 2011 2011 2012 2013 2014
Age 12 13 13 14 15 16
OpenBal 1100 901 834 700 521 365
IntroBal 1000 0 0 0 0 0
DistInc 1600 1700 1800 1900
Adjust 0 0 0 0 0 0

I think pivot might work but the aggregate function is throwing me,since no aggregation takes place.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-29 : 07:44:37

declare @tbl table (year int, age int, bal int)
insert @tbl values (2011,2,3), (2012,6,7), (2013,10,11), (2011,7,8)

;with cte as
(
select YEAR, s='age', val=age from @tbl
union all
select YEAR, s='bal', val=bal from @tbl
)
select s, [2011],[2012],[2013]
from
(select [year], s, val from cte) p
pivot
(
sum(val)
for year in ([2011],[2012],[2013])
) pvt

result
s 2011 2012 2013
---- ----------- ----------- -----------
age 9 6 10
bal 11 7 11

AbuGaarith
Starting Member

2 Posts

Posted - 2011-06-30 : 01:36:09

@nigelrivett thx ,however my final result should not 'sum' anything.It should keep 2011 as 2 cols and not join them.Also my big problem is; I don't know the amount of columns so it could be anything from 1 col to 25 cols,nor do I know the column heading, whether 1972 or 2012 ..meaning it could be any date range.The first result set is what I get back from sql but the report needs to look like the second result set.Hope u or some1 can assist

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-30 : 05:24:41

In that case you will need dynamic sql to construct the pivot
see
http://www.nigelrivett.net/SQLTsql/CrossTab.html

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources