group 變成一列....用逗號分隔 直的變橫的
例如
例如
Base Data:
DEPTNO ENAME
---------- ----------
20 SMITH
30 ALLEN
30 WARD
20 JONES
30 MARTIN
30 BLAKE
10 CLARK
20 SCOTT
10 KING
30 TURNER
20 ADAMS
30 JAMES
20 FORD
10 MILLER
Desired Output:
DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER 20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD
如果是要上面這樣的結果 11g很ez 只要像下面這樣寫
SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM emp
GROUP BY deptno;
但如果是10g就sad了 只能複雜寫法
with data
as
(
select deptno,
ename,
row_number() over (partition by deptnoorder by ename) rn,
count(*) over (partition by deptno) cnt
from enp
)
select deptno, ltrim(sys_connect_by_path(ename,','),',') scbp
from data
where rn = cnt
start with rn = 1
connect by prior deptno= deptnoand prior rn = rn-1
order by deptno
如果不只是一次查詢 就寫成function
寫法可去「http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php 」有詳細內容
SELECT division,
回覆刪除LTRIM(MAX(SYS_CONNECT_BY_PATH(deptno,','))
KEEP (DENSE_RANK LAST ORDER BY aaa),',') AS aaaaaaaaa
FROM (SELECT division,
deptno,
ROW_NUMBER() OVER (PARTITION BY division ORDER BY deptno) AS aaa,
ROW_NUMBER() OVER (PARTITION BY division ORDER BY deptno) -1 AS bbb
from b0030 where deptno in ('255044','257011','705000','702000'))
GROUP BY division
CONNECT BY bbb = PRIOR aaa AND division = PRIOR division
START WITH aaa = 1