How do I separate the values of one column into multiple columns In MS SQL-Server

Posted on

Question :

ID Contact No Ext Type
-- ---------- ---- ----
0001 75865558 123 work
0001 207586558 NULL home
0001 207586559 NULL cell
0001 746655558 321 work
0002 946655558 323 work
0002 2356841 NULL home
0003 6655558 NULL cell

I want to split the table up into

ID HPhone CPhone1 CPhone2 WPhone1 Ext1 WPhone2 Ext2
-- ---------- ---------- -------- --------- ---- --------- -----
0001 207586558 207586559 NULL 75865558 123 746655558 321
0002 2356841 NULL NULL 946655558 323 NULL NULL
0003 NULL 6655558 NULL NULL NULL NULL NULL

Any id can at most have 1 HPhone, 2CPhones, and 2 WPhones/Ext.

Im new to databases so Im not specifically looking to be given the answer but any advise about how I can split the first table into something similar to the second table would be appreciate.

Answer :

This sounds like a PIVOT, but those quickly become messy when you’re pivoting multiple things conditionally. So I would use MAX/CASE aggregates, like this (the CTE is used to give a deterministic order for numbers of the same type; if you want to change that, just change the ORDER BY inside the OVER() clause):

;WITH x AS 
(
  SELECT *, rn = ROW_NUMBER() OVER 
            (PARTITION BY ID, [Type] ORDER BY [Contact No])
  FROM dbo.SourceTable
)
SELECT ID, 
  HPhone  = MAX(CASE WHEN [Type] = 'home' THEN [Contact No] END),
  CPhone1 = MAX(CASE WHEN [Type] = 'cell' AND rn = 1 THEN [Contact No] END),
  CPhone2 = MAX(CASE WHEN [Type] = 'cell' AND rn = 2 THEN [Contact No] END),
  WPhone1 = MAX(CASE WHEN [Type] = 'work' AND rn = 1 THEN [Contact No] END),
  Ext1    = MAX(CASE WHEN [Type] = 'work' AND rn = 1 THEN [Ext] END),
  WPhone2 = MAX(CASE WHEN [Type] = 'work' AND rn = 2 THEN [Contact No] END),
  Ext2    = MAX(CASE WHEN [Type] = 'work' AND rn = 2 THEN [Ext] END)
FROM x
GROUP BY ID
ORDER BY ID;

Leave a Reply

Your email address will not be published.