如何在 MS SQL-Server 中將一列的值分成多列

`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`

我想把桌子分成

`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`

任何 id 最多可以有 1 個 HPhone、2CPhone 和 2 個 WPhone/Ext。

我是數據庫新手，所以我不是特別希望得到答案，但是任何關於如何將第一個表拆分為類似於第二個表的建議的建議將不勝感激。

這聽起來像一個PIVOT，但是當您有條件地旋轉多個事物時，這些很快就會變得混亂。所以我會使用MAX/CASE聚合，像這樣（CTE 用於為相同類型的數字提供確定性順序；如果你想改變它，只需改變子句ORDER BY內部）：OVER()

;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;

引用自：https://dba.stackexchange.com/questions/242188