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

Community · Accepted Answer · 2017-05-23 10:31:16Z

14

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

edited May 23, 2017 at 10:31

CommunityBot

11 silver badge

answered Oct 27, 2012 at 18:56

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alaa Alweish Over a year ago

Thanks for your contribution, both methods solved this issue, however if we take performance into consideration, which one is faster?

OutOFTouch Over a year ago

Can someone explain to me how MIN is going to work without a numeric value like this answer shows?

roman Over a year ago

as long as datatype is comparable you can apply min aggregate. You can compare strings, dates etc.

Taryn · Accepted Answer · 2012-10-28 13:23:21Z

6

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.

answered Oct 28, 2012 at 13:23

Taryn

249k57 gold badges375 silver badges409 bronze badges

2 Comments

roman Over a year ago

you don't need to use for xml when you just need to concat columns into variable. There also might be security issues when you trying to select data from table, for example, user can have permissions on procedure, but have no permissions on tables. Take a look at my answer here - stackoverflow.com/questions/13055295/…

Taryn Over a year ago

@RomanPekar there are a variety of different ways to concatenate columns, this is the method I choose to use.

Collectives™ on Stack Overflow

T:SQL: select values from rows as columns

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related