CODEWITHSUNDEEP
sqlsql-serverpivotaggregation
Derek Σωκράτης Finch
Derek Σωκράτης Finch

Reputation: 87

How to pivot values from rows to column based on value from related column

I am trying to pivot a set of data based on 3 value, so that each row represents all the profile URLs related to each ID, with each column relating to the profile code

This question is very similar to what I am trying to achieve, however it is not grouping the columns that are being pivoted based on another value: Pivot values on column based on grouped columns in SQL

So given the following example table:

Id        ProfileCode  ProfileURL  
-------------------------------------------------------
7ADC7368  IA           http://www.domainIA.com/profile1
5C627D6F  IA           http://www.domainIA.com/profile2
5C627D6F  AG           http://www.domainAG.com/profile1
5C627D6F  AF           http://www.domainAF.com/profile1
664B4AE9  IA           http://www.domainIA.com/profile3
664B4AE9  AF           http://www.domainAF.com/profile2

I am hoping to transform it into the following table:

Id        IA                                AG                                AF
-------------------------------------------------------------------------------------------------------------
7ADC7368  http://www.domainIA.com/profile1  null                              null
5C627D6F  http://www.domainIA.com/profile2  http://www.domainAG.com/profile1  http://www.domainAF.com/profile1
664B4AE9  http://www.domainIA.com/profile3  null                              http://www.domainAF.com/profile2

This is the code I have been trying to work, but I cannot find a way to relate the pivot to the association between the profile URL and its associated profile code. 

declare @tmp TABLE (Id NVARCHAR(15), ProfileCode NVARCHAR(2), ProfileURL NVARCHAR(50))

insert into @tmp (Id, ProfileCode, ProfileURL)
values  ('7ADC7368', 'IA', 'http://www.domainIA.com/profile1'),
        ('5C627D6F', 'IA', 'http://www.domainIA.com/profile2'),
        ('5C627D6F', 'AG', 'http://www.domainAG.com/profile1'),
        ('5C627D6F', 'AF', 'http://www.domainAF.com/profile1'),
        ('664B4AE9', 'IA', 'http://www.domainIA.com/profile3'),
        ('664B4AE9', 'AF', 'http://www.domainAF.com/profile2')

select 
     pvt.id,
    CASE
        WHEN ProfileCode = 'IA' THEN ProfileURL 
    END AS 'IA',
    CASE
        WHEN ProfileCode = 'AF' THEN ProfileURL 
    END AS 'AF',
    CASE
        WHEN ProfileCode = 'AG' THEN ProfileURL
    END AS 'AG'
from (  
    select 
         Id, ProfileCode, ProfileURL
        ,ROW_NUMBER() over(partition by ProfileCode order by ProfileURL) as RowNum
from 
    @tmp
) a
pivot (MAX(ProfileCode) for RowNum in ('IA', 'AF', 'AG') as pvt

I greatly would appreciate any help or pointers with what I am trying to achieve.

Upvotes: 1

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Just use conditional aggregation:

SELECT id,
       MAX(CASE WHEN ProfileCode = 'IA' THEN ProfileURL END) AS IA,
       MAX(CASE WHEN ProfileCode = 'AF' THEN ProfileURL END) AS AF,
       MAX(CASE WHEN ProfileCode = 'AG' THEN ProfileURL END) AS AG
FROM @tmp t
GROUP BY id;

You only need ROW_NUMBER() if you have multiple of the same code for a given id and you want the results on separate rows. Your sample data and current logic suggest that this is not the case.

Upvotes: 1

Related Questions