T:SQL: select values from rows as columns

pivot, sql, sql-server, t-sql

Solution

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

Problem

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 ```

Original source