T:SQL: select values from rows as columns

Question

I have a table for Profiles stores profile properties values in row style, ex:

[ProfileID]     [PropertyDefinitionID]      [PropertyValue]
1               6                           Jone
1               7                           Smith
1               8                           Mr
1               3                           50000

and another table for property definitions :

[PropertyDefinitionID]  [PropertyName]
6                       FirstName
7                       LastName
8                       Prefix
3                       Salary

How to use PIVOT or any other way to show it in this way:

[ProfileID] [FirstName] [LastName]  [Salary]
1           Jone        Smith       5000

Solution 1

It's easy to do this without PIVOT keyword, just by grouping

select
    P.ProfileID,
    min(case when PD.PropertyName = 'FirstName' then P.PropertyValue else null end) as FirstName,
    min(case when PD.PropertyName = 'LastName' then P.PropertyValue else null end) as LastName,
    min(case when PD.PropertyName = 'Salary' then P.PropertyValue else null end) as Salary
from Profiles as P
    left outer join PropertyDefinitions as PD on PD.PropertyDefinitionID = P.PropertyDefinitionID
group by P.ProfileID

you can also do this with PIVOT keyword

select
    *
from
(
    select P.ProfileID, P.PropertyValue, PD.PropertyName
    from Profiles as P
        left outer join PropertyDefinitions as PD on PD.PropertyDefinitionID = P.PropertyDefinitionID
) as P
    pivot
    (
        min(P.PropertyValue)
        for P.PropertyName in ([FirstName], [LastName], [Salary])
    ) as PIV

UPDATE: For dynamic number of properties - take a look at Increment value in SQL SELECT statement

Solution 2

It looks like you might have an unknown number of PropertyName's that you need to turn into columns. If that is the case, then you can use dynamic sql to generate the result:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(PropertyName) 
                    from propertydefinitions
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT profileid, ' + @cols + ' from 
             (
                select p.profileid,
                  p.propertyvalue,
                  d.propertyname
                from profiles p
                left join propertydefinitions d
                  on p.PropertyDefinitionID = d.PropertyDefinitionID
            ) x
            pivot 
            (
                max(propertyvalue)
                for propertyname in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo.

Comments

[Roman Pekar]

you don't need to use for xml when you just need to concat columns into variable. There also might be security issues when you trying to select data from table, for example, user can have permissions on procedure, but have no permissions on tables. Take a look at my answer here - stackoverflow.com/questions/13055295/…

[Taryn]

@RomanPekar there are a variety of different ways to concatenate columns, this is the method I choose to use.

[Alaa]

Thanks for your contribution, both methods solved this issue, however if we take performance into consideration, which one is faster?