T:SQL: select values from rows as columns

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 Salaryfrom Profiles as P    left outer join PropertyDefinitions as PD on PD.PropertyDefinitionID = P.PropertyDefinitionIDgroup 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

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.