我正在尋找 SQL 問題的解決方案：
我有這樣的輸入表：
| RID | 援助 | 型別 |
|---|---|---|
| 76 | 734 | TKI |
| 76 | 528 | NPlat |
| 76 | 735 | TKI |
| 77 | 713 | 平臺 |
| 77 | 749 | IO |
| 77 | 739 | TKI |
| 77 | 714 | NPlat |
| 78 | 518 | 平臺 |
| 73 | 519 | 平臺 |
| 73 | 518 | 平臺 |
我想要這種輸出：
| RID | TKI | IO | NPlat | 平臺 |
|---|---|---|---|---|
| 73 | 518, 519 | |||
| 76 | 734, 735 | 528 | ||
| 77 | 739 | 749 | 714 | 713 |
| 78 | 518 |
我試過了PIVOT，但它不起作用。也試過和GROUP BYandPARTITION BY一起，但沒有成功。
任何人都可以有任何想法來解決這個問題嗎？
注意：我使用的是 Microsoft SQL Server 2016 (SP3)。
uj5u.com熱心網友回復：
您可以先連接Aid成csv格式，然后執行樞軸
select *
from (
select Rid, [Type],
Aid = stuff(
(select ',' convert(varchar(10), x.Aid)
from tbl x
where x.Rid = t.Rid
and x.[Type] = t.[Type]
order by x.Aid
for xml path('')), 1, 1, '')
from tbl t
group by Rid, [Type]
) d
pivot
(
max(Aid)
for [Type] in ([TKI], [IO], [NPlat], [Plat])
) p
dbfiddle 演示
uj5u.com熱心網友回復：
簡單使用 Pivot 的東西：
SELECT RID,[TKI],[IO],[NPlat],[Plat]
FROM
(SELECT RID,TYPE,AID = STUFF((
SELECT ',' CONVERT(VARCHAR,AID)
FROM test t
WHERE t.RID = test.RID AND t.TYPE = test.type
FOR XML PATH('')
), 1, 1, '')
FROM test
)sorce
PIVOT
(
max(AID) FOR type IN ([TKI],[IO],[NPlat],[Plat])
) AS PivotTable
uj5u.com熱心網友回復：
SQL Server 2016：
一個可能的解決方案是分組和聚合：
桌子：
SELECT *
INTO Data
FROM (VALUES
(76, 734, 'TKI'),
(76, 528, 'NPlat'),
(76, 735, 'TKI'),
(77, 713, 'Plat'),
(77, 749, 'IO'),
(77, 739, 'TKI'),
(77, 714, 'NPlat'),
(78, 518, 'Plat'),
(73, 519, 'Plat'),
(73, 518, 'Plat')
) v (RId, AId, [Type])
陳述：
SELECT
d.Rid,
TKI = STUFF(
(
SELECT CONCAT(',', AId)
FROM Data
WHERE RId = d.RId AND [Type] = 'TKI'
ORDER BY AId
FOR XML PATH('')
), 1, 1, ''
),
IO = STUFF(
(
SELECT CONCAT(',', AId)
FROM Data
WHERE RId = d.RId AND [Type] = 'IO'
ORDER BY AId
FOR XML PATH('')
), 1, 1, ''
),
NPlat = STUFF(
(
SELECT CONCAT(',', AId)
FROM Data
WHERE RId = d.RId AND [Type] = 'NPlat'
ORDER BY AId
FOR XML PATH('')
), 1, 1, ''
),
Plat = STUFF(
(
SELECT CONCAT(',', AId)
FROM Data
WHERE RId = d.RId AND [Type] = 'Plat'
ORDER BY AId
FOR XML PATH('')
), 1, 1, ''
)
FROM Data d
GROUP BY d.RId
SQL Server 2017 ：
PIVOT并且STRING_AGG()可能是第一個選擇：
SELECT *
FROM (
SELECT RId, STRING_AGG(AId, ',') WITHIN GROUP (ORDER BY AId) AS AId, [Type]
FROM Data
GROUP BY RId, [Type]
) t
PIVOT (
MAX(AId)
FOR [Type] IN ([TKI], [IO], [NPLat], [Plat])
) p
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/422525.html
標籤：
上一篇：按生效日期拆分記錄