converting columns to rows. - 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)

converting columns to rows.

Author

Topic

rum23
Yak Posting Veteran

77 Posts

Posted - 2009-02-06 : 13:27:47

I have a table with the following columns:

ProjectID - D_ReclaimPressTest - D_WaterPressTest - D_SewerLineInsp

an example of the data in this would be

B11 - 2002-11-11 00:00:00.000 - 2006-06-11 00:00:00.000 - 2002-05-15 00:00:00.000

I am trying to show the results like this in a separate view

ProjectID - TestName - TestDate
B11 - D_ReclaimPressTest - 2002-11-11 00:00:00.000
B11 - D_WaterPressTest - 2006-06-11 00:00:00.000
B11 - D_SewerLineInsp - 2002-05-15 00:00:00.000

How can I do this? Please help.

rum23
Yak Posting Veteran

77 Posts

Posted - 2009-02-06 : 13:33:27

got it!

Select ProjectID, TestName, TestDate
FROM
(SELECT ProjectID, D_ReclaimPressTest, D_WaterPressTest, D_SewerLineInsp FROM tblProjects)p
UNPIVOT
(TestDate FOR TestName IN
(D_ReclaimPressTest, D_WaterPressTest, D_SewerLineInsp)
)AS unpvt

Thanks anyway!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-06 : 13:43:10

[code]Select ProjectID,'D_ReclaimPressTest' as TestName,D_ReclaimPressTest as TestDate
from table
union all
Select ProjectID,'D_WaterPressTest' ,D_WaterPressTest
from table
union all
Select ProjectID,'D_SewerLineInsp' ,D_SewerLineInsp
from table[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-07 : 08:43:22

also see

http://technet.microsoft.com/en-us/library/ms177410.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources