我想將主席和聯合主席的成果合并為一欄，但我不知道如何去做。我確實嘗試concat過主席和聯合主席這兩個領域，但沒有奏效。任何想法我怎么能做到這一點？
Microsoft SQL Server 2016 (SP3) (KB5003279) - 13.0.6300.2 (X64)
CREATE TABLE #temp2 (
University varchar(255),
ChairRole varchar(255)
);
Insert into #temp2 (University ,ChairRole) VALUES ('Test 1','Chair')
Insert into #temp2 (University ,ChairRole) VALUES ('Test 2','Chair')
Insert into #temp2 (University ,ChairRole) VALUES ('Test 3','Vice-Chair')
Insert into #temp2 (University ,ChairRole) VALUES ('Test 4','Co-Chair')
SQL
select
University,
Chair=isnull([Chair],0),
[Vice-Chair]=isnull([Vice-Chair],0),
[Co-Chair]=isnull([Co-Chair],0)
from #temp2
Pivot (
SUM(Total)
for [ChairRole] in ([Chair],[Vice-Chair],[Co-Chair])
) as p
where University is not null
ORDER BY 3 DESC
FOR XML PATH('University'),type)
FOR XML PATH('Report')
當前結果
<University>
<University>University/University>
<Chair>0</Chair>
<Vice-Chair>2</Vice-Chair>
<Co-Chair>1</Co-Chair>
</University >
結果我想做
<University>
<University>University/University>
<Chair>1</Chair>
<Vice-Chair>2</Vice-Chair>
</University >
uj5u.com熱心網友回復：
只需使用 做一個 Concat 并將其加起來
select
University,
Chair=isnull([Chair],0) isnull([Co-Chair],0) ,
[Vice-Chair]=isnull([Vice-Chair],0)
from #temp2
Pivot (
SUM(Total)
for [ChairRole] in ([Chair],[Vice-Chair],[Co-Chair])
) as p
where University is not null
ORDER BY 3 DESC
FOR XML PATH('University'),type)
FOR XML PATH('Report')
如果您沒有看到這有效，請嘗試使用這樣的 SUB 查詢
SELECT
University,
Chair = ISNULL(Chair,0) isnull([Vice-Chair],0),
[Co-Chair]
FROM
(
select
University,
Chair=isnull([Chair],0),
[Vice-Chair]=isnull([Vice-Chair],0),
[Co-Chair]=isnull([Co-Chair],0)
from #temp2
Pivot (
SUM(Total)
for [ChairRole] in ([Chair],[Vice-Chair],[Co-Chair])
) as p
where University is not null
)Q
ORDER BY 3 DESC
FOR XML PATH('University'),type
FOR XML PATH('Report')
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/347867.html
標籤：sql sql-server