A Quick Tutorial on SQL 2005 PIVOT T-SQL COMMAND
A PIVOT command in SQL will transpose a particular column into row. For example, let’s study the following SQL command to obtain (userid, username, firstname, lastname, email, displayname, propertyvalue, propertyname, portalID) from a DotNetNuke database.
select U.userid,U.username,U.firstname,U.lastname,U.email,U.Displayname,UP.propertyvalue,
P.propertyname, P.PortalID into ##tempDUDProfile
from Users U inner join userprofile UP on U.userid = UP.userid
inner join profilepropertydefinition P on UP.propertydefinitionid = P.propertydefinitionid
where P.Visible=1
AND P.Deleted=0
AND P.PRopertyName <> 'FirstName'
AND P.PropertyName <> 'LastName'
AND P.PRopertyNAme <> 'E-Mail'
AND P.PRopertyNAme <> 'Email'
AND P.PRopertyNAme <> 'Username'
AND P.PRopertyNAme <> 'DisplayName'
AND P.PropertyName Not Like '%[.#)(&[@!^!]]%' ESCAPE '!'
Note that I would like to transpose each unique entry in the column “propertyname” into their own distinct column name (TimeZone, PreferredLocale, etc.)
This is where the PIVOT command comes in handy.
DECLARE @columns VARCHAR(8000)
SELECT @columns = COALESCE(@columns + ',[' + cast(propertyname as varchar) + ']',
'[' + cast(propertyname as varchar)+ ']')
FROM ##tempDUDProfile
GROUP BY propertyname
DECLARE @query VARCHAR(8000)
SET @query = '
SELECT *
FROM ##tempDUDProfile
PIVOT
(
MAX(propertyvalue)
FOR [propertyname]
IN (' + @columns + ')
)
AS p
'
EXECUTE(@query)
NOTE: The COALESCE command will return only profile property that has values. So if all the users do not have a profile set, the default would be NULL and it would not generate a column for this property.
