sql server - 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

Answer 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