CODEWITHSUNDEEP
sqlsql-servert-sqlpivot
Alaa Alweish
Alaa Alweish

Reputation: 9084

T:SQL: select values from rows as columns

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

Upvotes: 8

Views: 39537

Answers (2)

roman
roman

Reputation: 117606

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

Upvotes: 14

Taryn
Taryn

Reputation: 247880

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.

Upvotes: 6

Related Questions