Pivot 3 columns - 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 2005 Forums

Transact-SQL (2005)

Pivot 3 columns

Author

Topic

ecsmoore
Starting Member

5 Posts

Posted - 2012-04-09 : 17:54:41

Does anyone know if it is possible to pivot this table. I need year as column names and the other 3 columns as rows.
I have tried about 20 different ways over 3 days and keep getting no where.
ID YearN EI Booked Rooms
1 2008 990000.00 3 0
2 2009 7007257.50 44 0
3 2010 5956687.50 61 380
4 2011 1009972.00 35 20
5 2012 886590.00 27 10
6 2013 524737.00 3 0
7 2014 224250.00 2 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-10 : 16:22:00

you can it would be something like


SELECT *
FROM 
(SELECT YearN,Category,Value
 FROM table t
 UNPIVOT (Value FOR Category IN ([EI],[Booked],[Rooms]))c
)m
PIVOT (MAX(Value) FOR YearN IN ([2008],[2009],[2010],[2011],...))p

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

ecsmoore
Starting Member

5 Posts

Posted - 2012-04-11 : 16:31:37

Thank you so much. This worked like a charm.
So many people looking for this....

quote:
Originally posted by visakh16

you can it would be something like

SELECT *
FROM
(SELECT YearN,Category,Value
FROM table t
UNPIVOT (Value FOR Category IN ([EI],[Booked],[Rooms]))c
)m
PIVOT (MAX(Value) FOR YearN IN ([2008],[2009],[2010],[2011],...))p

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-12 : 01:06:11

wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources