如何將列的結果從多行連接到單行？我的查詢是：
SELECT id AS "Record ID", ins_type AS "Inspection Type", stat AS "Status"
FROM records
ORDER BY id;
結果：
在 id 相同的情況下，我想將任何多個檢查型別聚合到一個列中，如下所示：
Record ID Inspection Type Status
B202105148 Final Building, Final Electrical, Framing & Flashing Approved
B202105143 Roof Framing/Sheathing Approved
我正在使用 Oracle 資料庫版本 12.1.0.2.0。
我嘗試使用LISTAGG()函式：
SELECT id AS "Record ID", LISTAGG( ins_type, ', ' ) WITHIN GROUP ( ORDER BY ins_type ) AS "Inspection Type", stat AS "Status"
FROM records
ORDER BY id;
但我得到了：
ORA-00937: not a single-group group function.
uj5u.com熱心網友回復：
你錯過了分組
SELECT id AS "Record ID",
LISTAGG( ins_type, ', ' ) WITHIN GROUP ( ORDER BY ins_type ) AS "Inspection Type", stat AS "Status"
FROM records
group by id,stat
ORDER BY id;
uj5u.com熱心網友回復：
來自評論：
如果框架和閃爍未獲批準，預期結果是什么？
預計會出現空值。
您需要在列上使用聚合函式stat或將其添加到GROUP BY子句中。由于您似乎只想顯示Approved所有記錄，Approved那么您可以使用：
SELECT id AS "Record ID",
LISTAGG( ins_type, ', ' ) WITHIN GROUP ( ORDER BY ins_type ) AS "Inspection Type",
CASE
WHEN COUNT(CASE WHEN stat = 'Approved' THEN 1 END) = COUNT(*)
THEN 'Approved'
END AS "Status"
FROM records
GROUP BY id
ORDER BY id;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/432930.html
下一篇：匿名塊中的Oracle注釋