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 :

  • Operand type clash: int is incompatible with date + The INSERT statement conflicted with the FOREIGN KEY constraint
  • views based on a view is slow
  • On a Heap Table, what does a non-clustered index use as a pointer to a row?
  • concatenating results from SQL query and NULL columns
  • Cannot execute synonym stored procedure with SQL Server ODBC Driver; works with OLEDB
  • Passing an object collection as a parameter into SQL Server stored procedure
  • [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
    

  • SSIS Condition: How to get results from the past 24 hours
  • Mathematical Function within Sql Case Statement
  • Error on renaming database in SQL Server 2008 R2
  • Set database from SINGLE USER mode to MULTI USER
  • How to insert data into SQL Server
  • Reverse deployment
  • 2 Solutions collect form web for “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 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

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.