根據我的要求，我需要在同一列上應用兩次資料透視以獲得不同的值作為列標題。請參閱下表中資料的顯示方式和預期結果。
桌子
| 問題 | 回復 | 翻譯的回應 | 活動ID | 創建于 |
|---|---|---|---|---|
| 原因 | 測驗 | 睪丸 | 1 | 01-01-2022 |
| 電子郵件 | [email protected] | [email protected] | 1 | 01-01-2022 |
| 行動 | 測驗 | 遺囑 | 1 | 01-01-2022 |
期望的輸出：
| 電子郵件 | 原因 | 行動 | 翻譯行動 | 翻譯原因 | 活動ID | 創建于 |
|---|---|---|---|---|---|---|
| [email protected] | 測驗 | 測驗 | 遺囑 | 睪丸 | 1 | 01-01-2022 |
我通過使用下面的查詢實作了這一點，它通過使用帶有兩個不同樞軸選擇陳述句的內部連接來作業。
SELECT A.activityid,
A.createdon,
A.[Email],
A.[Action],
A.[Reason],
B.[Translated Action],
B AS [Translated Reason]
FROM (SELECT *
FROM (SELECT activityid, createdon, question, response FROM Table) subquery
PIVOT (MAX(response)
FOR question IN ([Email], [Reason], [Action])) pv1) A
INNER JOIN (SELECT activityid,
createdon,
question,
translatedresponse,
[Action] AS [Translated Action],
[Reason] AS [Translated Reason]
FROM (SELECT activityid,
createdon,
question,
response,
translatedresponse
FROM Table) subquery
PIVOT (MAX(translatedresponse)
FOR question IN ([Email], [Action], [Reason])) pv2) B ON A.activityid = B.activityid;
是否有另一種撰寫 SQL 查詢的方法來優化和擺脫內部連接。
uj5u.com熱心網友回復：
使用條件聚合：
SELECT MAX(CASE Question WHEN 'Email' THEN Response END) AS Email,
MAX(CASE Question WHEN 'Reason' THEN Response END) AS Reason,
MAX(CASE Question WHEN 'Action' THEN Response END) AS Action,
MAX(CASE Question WHEN 'Reason' THEN TranslatedResponse END) AS TranslatedReason,
MAX(CASE Question WHEN 'Action' THEN TranslatedResponse END) AS TranslatedAction,
activityid,
createdon
FROM dbo.YourTable
GROUP BY activityid,
createdon;
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/404960.html
標籤：
上一篇：包括分組查詢的中位數。
下一篇：更好的方法（設定和呼叫引數）