Convert colums in a table to row.

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)

Convert colums in a table to row.

Author

Topic

rum23
Yak Posting Veteran

77 Posts

Posted - 2009-05-14 : 11:29:28

Right now, the data is stored in the table in the format below.

FY Q1Cost Q2Cost Q3Cost Q4Cost
---------------------------------------------------------------------
2008 0.00 0.00 249166.665 249166.665
2009 269702.38 269702.38 269702.38 269702.38
2010 240952.3833 240952.3833 240952.3833 0.00

and I want to write SQL to get the data in this format.

Quarter 2008 2009 2010
---------------------------------------------------------------------
Q1Cost 0.00 269702.38 240952.3833
Q2Cost 0.00 269702.38 240952.3833
Q3Cost 249166.665 269702.38 240952.3833
Q4Cost 249166.665 269702.38 0.00

How can I achieve this in SQL? Please help

Thanks a lot!

-rum23

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-14 : 11:37:33

[code]SELECT Quarter,
MAX(CASE WHEN FY='2008' THEN Cost ELSE NULL END) AS [2008],
MAX(CASE WHEN FY='2009' THEN Cost ELSE NULL END) AS [2009],
MAX(CASE WHEN FY='2010' THEN Cost ELSE NULL END) AS [2010]
FROM
(
SELECT FY,Quarter,Cost
FROM YourTable t
UNPIVOT (Cost FOR Quarter IN ([Q1Cost],[Q2Cost],[Q3Cost],[Q4Cost]))u
)m
GROUP BY Quarter
[/code]

rum23
Yak Posting Veteran

77 Posts

Posted - 2009-05-14 : 12:39:24

oh wow! Awesome! thanks much

rum23
Yak Posting Veteran

77 Posts

Posted - 2009-05-15 : 11:02:44

One small problem...

The Fiscal Years(FY) in the table below are not fixed to 2008, 2009, 2010, etc...they can be anything year. visakh16, in the sql that you mentioned below, is there any way to not harcode the values checks for '2008', 2009 ?

Thanks much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-15 : 13:46:49

yup. there's a way. but you've to use dynamic sql for that.see below


DECLARE @YearList varchar(max),@Sql varchar(max)

SELECT @YearList=STUFF((SELECT DISTINCT ','+ CAST(FY AS varchar(4)) FROM YourTable FOR XML PATH('')),1,1,'')

SET @Sql='SELECT
FROM (SELECT FY,Quarter,Cost
FROM YourTable t
UNPIVOT (Cost FOR Quarter IN ([Q1Cost],[Q2Cost],[Q3Cost],[Q4Cost]))u)m
PIVOT (SUM(Cost) FOR FY IN ([' + REPLACE(@YearList,',','],[')+ ']))p'
 EXEC(@Sql)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources