Pivot table - 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

General SQL Server Forums

New to SQL Server Programming

Pivot table

Author

Topic

asiandoll
Starting Member

1 Post

Posted - 2012-04-30 : 13:05:12

I have a table with the following data

Category Year Actual Budget
Pen 2012 100 1000
Water 2013 200 400
Pen 2014 1000 1000

What I want to actually show in the Pivot table:

2012 2013 2014
Category Actual Budget Actual Budget Actual Budget
Pen 100 1000 1000 1000
Water 200 400

How can I achieve this?

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-04-30 : 14:04:47

Try this

SELECT CATEGORY ,[2012],[2013],[2014]
FROM ( SELEC CATEGORY ,YEAR ,ACTUALBUGDET
FROM TABLE) AS TAB

PIVOT ( AVG(ACTUALBUGDET) FOR YEAR IN([2012],[2013],[2014])) AS pvt

If this is not giving you proper value.Then Please provide the proper data with proper formatting..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-30 : 14:30:59

[code]
SELECT Category,
SUM(CASE WHEN Year =2012 THEN ACtual END) AS Actual2012,
SUM(CASE WHEN Year =2012 THEN Budget END) AS Budget2012,
SUM(CASE WHEN Year =2013 THEN ACtual END) AS Actual2013,
SUM(CASE WHEN Year =2013 THEN Budget END) AS Budget2013,
SUM(CASE WHEN Year =2014 THEN ACtual END) AS Actual2014,
SUM(CASE WHEN Year =2014 THEN Budget END) AS Budget2014
FROM Table
GROUP BY Category
[/code]

if you want exact format as you posted in output above you need to do this in some reporting tool like SSRS using column groups.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources