這里上表是我的輸入，下表是我需要的輸出。
uj5u.com熱心網友回復：
listagg一個簡單的選項是使用并將分隔符設定為換行符來聚合它們：
樣本資料：
SQL> select * from temp;
WO TITLE
-- --------------------
W1 T1
W1 T2
W2 TT1
詢問：
SQL> select workflow_id,
2 listagg(title, chr(10)) within group (order by title) as title
3 from temp
4 group by workflow_id;
WO TITLE
-- --------------------
W1 T1
T2
W2 TT1
SQL>
uj5u.com熱心網友回復：
與新的行分隔符一起使用LISTAGG（假設您已經訂購了行）：
SELECT workflow_id,
LISTAGG(title, CHR(10)) WITHIN GROUP (ORDER BY ROWNUM) AS titles
FROM table_name
GROUP BY workflow_id;
其中，對于樣本資料：
CREATE TABLE table_name (workflow_id, title) AS
SELECT 'W1', 'd1' FROM DUAL UNION ALL
SELECT 'W1', 'd5' FROM DUAL UNION ALL
SELECT 'W1', 'd4' FROM DUAL UNION ALL
SELECT 'W2', 'd2' FROM DUAL UNION ALL
SELECT 'W2', 'd3' FROM DUAL;
輸出：
WORKFLOW_ID 標題 W1 d1
d5
d4W2 d2
d3
db<>在這里擺弄
uj5u.com熱心網友回復：
這在這里有效，希望它可以幫助......
WITH
tbl AS
(
Select 'w1' "WORKFLOW_NO", 'd1' "DISCIPLINE" From Dual UNION ALL
Select 'w1' "WORKFLOW_NO", 'd5' "DISCIPLINE" From Dual UNION ALL
Select 'w1' "WORKFLOW_NO", 'd4' "DISCIPLINE" From Dual UNION ALL
Select 'w2' "WORKFLOW_NO", 'd2' "DISCIPLINE" From Dual UNION ALL
Select 'w2' "WORKFLOW_NO", 'd3' "DISCIPLINE" From Dual
)
SELECT
SUBSTR(LISTAGG(' ' || Chr(10) || WORKFLOW_NO) WITHIN GROUP (ORDER BY WF), 3, 50) "WORKFLOW_NO",
LISTAGG(DISCIPLINE, Chr(10)) WITHIN GROUP (ORDER BY WF) "DISCIPLINE"
FROM
(
SELECT
c.WORKFLOW_NO "WF",
c.CNT "CNT",
CASE
WHEN Count(1) OVER(PARTITION BY t.WORKFLOW_NO ORDER BY t.WORKFLOW_NO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) = c.CNT
THEN t.WORKFLOW_NO
ELSE ''
END "WORKFLOW_NO",
t.DISCIPLINE "DISCIPLINE"
FROM
tbl t
INNER JOIN
(
SELECT DISTINCT
WORKFLOW_NO "WORKFLOW_NO",
Count(WORKFLOW_NO) OVER(PARTITION BY WORKFLOW_NO ORDER BY WORKFLOW_NO) "CNT"
FROM
tbl
) c ON(c.WORKFLOW_NO = t.WORKFLOW_NO)
)
GROUP BY
WF, CNT
--
-- R e s u l t
-- WORKFLOW_NO DISCIPLINE
-- ------------ ----------
-- d1
-- d4
-- w1 d5
-- -----------------------------------------
-- d2
-- w2 d3
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/484724.html