2013年5月23日

group_concat (mysql) 的oracle用法

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  」有詳細內容




1 則留言:

  1. 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

    回覆刪除