考慮我有一張這樣的桌子
ID DEPARTMENT
1 Production
1 IT
2 Sales
2 Marketing
2 Sales
我怎樣才能得到如下結果
ID Production IT Sales Marketing
1 1 1 0 0
2 0 0 1 1
2 0 0 1 0
您會注意到，在原始表中 ID 號為 2 的“銷售”部門是重復的，因此每當一個部門重復時，透視表中都應該有另一個條目。
uj5u.com熱心網友回復：
一種方法是在旋轉之前添加一個 row_number。
然后樞軸將不只是按 ID 分組。
SELECT ID, Production, IT, Sales, Marketing
FROM
(
SELECT ID, DEPARTMENT
, ROW_NUMBER() OVER (PARTITION BY ID, DEPARTMENT ORDER BY ID) AS RN
FROM yourtable t
) Src
PIVOT
(
COUNT(*)
FOR DEPARTMENT IN (
'Production' AS Production,
'IT' AS IT,
'Sales' AS Sales,
'Marketing' AS Marketing
)
) Pvt
| ID | 生產 | 它 | 銷售量 | 營銷 |
|---|---|---|---|---|
| 1 | 1 | 1 | 0 | 0 |
| 2 | 0 | 0 | 1 | 1 |
| 2 | 0 | 0 | 1 | 0 |
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/368557.html