Separate values into multiple columns

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. The GUI only has fields for 2 work phones and 2 cell phone; however, in the table cell1 and cell2 both have the type of 'cell' and same for work1 and work2.

I'm new to databases so I'm 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 appreciated.

Answer 1

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;