Splitting SQL column into multiple columns based on value

Question

I have a table like the below

Opp_ID     Role_Name     Role_User_Name
---------------------------------------
1          Lead          Person_one
1          Developer     Person_two
1          Developer     Person_three
1          Owner         Person_four
1          Developer     Person_five

I now need to split the Role_Name column to be 3 different columns based on the values. I need to make sure there are no NULL values so the table should like the below

Opp_ID     Lead        Developer     Owner
--------------------------------------------------
1          Person_one  Person_two    Person_four
1          Person_one  Person_three  Person_four
1          Person_one  Person_five   Person_four

My code is currently:

SELECT
    ID,
    CASE WHEN Role_Name = 'Lead' THEN Role_User_Name ELSE NULL END AS Lead,
    CASE WHEN Role_Name = 'Developer' THEN Role_User_Name ELSE NULL END AS Developer,
    CASE WHEN Role_Name = 'Owner' THEN Role_User_Name ELSE NULL END AS Owner
FROM 
    [table1]
WHERE 
    Role_Name IN ('Lead','Developer','Owner')

Unfortunately this returns these results:

Opp_ID     Lead        Developer     Owner
-------------------------------------------
1          Person_one  NULL          NULL
1          NULL        Person_two    NULL
1          NULL        Person_three  NULL
1          NULL        NULL          Person_four
1          NULL        Person_five   NULL

I assume to get this working you need to join the code back on itself but I can't seem to get it working.

This is a PIVOT operation, but how that is accomplished will depend on the database you are using. — Shawn
– Shawn, Commented Jun 18, 2018 at 14:14
In your ELSE NULL why don't you put ELSE Role_User_Name ? Also what should be the result in the NULL values of Developer in your last example? — Alexandre Elshobokshy
– Alexandre Elshobokshy, Commented Jun 18, 2018 at 14:23
I was wrong. You don't need a PIVOT but a couple of self-JOINs. — Shawn
– Shawn, Commented Jun 18, 2018 at 14:44

Shawn · Accepted Answer · 2018-06-18 14:35:55Z

1

To apply each developer and lead across your owners for an Opp_ID, you'll want something like:

SELECT o.opp_id
    , o.Role_User_Name AS Owner
    , l.Role_User_Name AS Lead
    , d.Role_User_Name AS Developer
FROM t1 AS o
LEFT OUTER JOIN t1 l ON o.opp_id = l.opp_id AND l.Role_Name = 'Lead'
LEFT OUTER JOIN t1 d ON o.opp_id = d.opp_id AND d.Role_Name = 'Developer'
WHERE o.Role_Name = 'Owner'

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=da4daea062534245bed474f93ffafbb7

answered Jun 18, 2018 at 14:35

Shawn

4,7861 gold badge22 silver badges29 bronze badges

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

1 Comment

Shawn Over a year ago

dbfiddle.uk/… <<< Multiple Owners and Leads, and an Opp_ID without a Lead.

Gordon Linoff · Accepted Answer · 2018-06-18 14:26:00Z

1

You can just switch to aggregation:

SELECT ID,
       MAX(CASE WHEN Role_Name = 'Lead' THEN Role_User_Name END) AS Lead,
       MAX(CASE WHEN Role_Name = 'Developer' THEN Role_User_Name END) AS Developer,
       MAX(CASE WHEN Role_Name = 'Owner' THEN Role_User_Name END) AS Owner
FROM [table1]
WHERE  Role_Name IN ('Lead', 'Developer', 'Owner')
GROUP BY ID;

If you could have multiple people, you might want to use STRING_AGG().

Note that I removed the ELSE NULL. This is redundant. With no ELSE clause, the CASE expression returns NULL when there is no match.

answered Jun 18, 2018 at 14:26

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

DhruvJoshi · Accepted Answer · 2018-06-18 14:33:12Z

0

You can also use first_value function on Pivot results like below

See working demo

select 
    opp_id,
    lead=COALESCE([lead],FIRST_VALUE([Lead]) over( order by opp_id )),
    Developer=COALESCE([Developer],FIRST_VALUE([Developer]) over( order by opp_id )),
    Owner=COALESCE([Owner],FIRST_VALUE([Owner]) over( order by opp_id ))
from 
(select opp_id,Role_Name,
 Role_User_Name,
 rn=row_number() over( partition by Role_Name order by (select 1))
 from
 table1)
 src
pivot
(max(Role_user_name) for role_name in ([Lead],[Developer],[Owner]))p

answered Jun 18, 2018 at 14:33

DhruvJoshi

17.2k6 gold badges46 silver badges63 bronze badges

Comments

zulmach · Accepted Answer · 2022-02-24 07:55:44Z

0

I prefer to use cross join

select o.opp_id,
   o.role_user_name o, 
   l.role_user_name l, 
   d.role_user_name d
from t1 o cross join t1 l cross join t1 d
where o.role_name = 'Owner' 
   and l.role_name = 'Lead' 
   and d.role_name = 'Developer'

enter image description here

answered Feb 24, 2022 at 7:55

zulmach

11 bronze badge

Collectives™ on Stack Overflow

Splitting SQL column into multiple columns based on value

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related