Friday, May 29, 2009

How to convert from rows to columns format in the table

I have the table and the values are defined as below:
Id PuringInterval Description
1 10 UTM
2 20 Hourly
3 25 Daily

And I would like to have the select or the view statement to return the
following
UTM Hourly Daily
10 20 25
So I want to convert from rows to columns

In SQL Server 2005 you can use the PIVOT operator, and using CASE will work
for both SQL Server 2000 & 2005:

SQL Server 2005
SELECT MAX(UTM) AS 'UTM',
MAX(Hourly) AS 'Hourly',
MAX(Daily) AS 'Daily'
FROM Foobar
PIVOT (MAX(PuringInterval)
FOR [Description] IN ([UTM], [Hourly], [Daily])) AS P;

SQL Server 2000 & 2005


SELECT MAX(CASE WHEN [Description] = 'UTM'
THEN PuringInterval END) AS 'UTM',
MAX(CASE WHEN [Description] = 'Hourly'
THEN PuringInterval END) AS 'Hourly',
MAX(CASE WHEN [Description] = 'Daily'
THEN PuringInterval END) AS 'Daily'
FROM Foobar;

Some more information refer link
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx


No comments:

Post a Comment