MySQL] Row합치기
SELECT GROUP_CONCAT(NAME SEPARATOR ';') FROM USER_TEST GROUP BY GROUPNUM;
출처 : http://xe.issro.net/MySQL/297
출처 : http://yangwenry.springnote.com/pages/233637
- 예제쿼리
emp.ename 컬럼, 그리고 그룹번호로 emp.deptno 컬럼을 예로 들어 쿼리를 구성해 보자.
emp 테이블의 deptno, ename 컬럼은 아래와 같다.
10 CLARK
10 KING
10 MILLER
20 ADAMS
20 FORD
20 JONES
20 SCOTT
20 SMITH
30 ALLEN
30 BLAKE
30 JAMES
30 MARTIN
30 TURNER
30 WARD
이것을
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
요렇게 바꿔보자...
최초에 제시한 두 개의 쿼리들에 대해서,
오라클 개발자인 Tom Kyte가 제시한 쿼리들을 참고용으로 남기도록 하겠습니다.
SELECT DEPTNO
, LTRIM (SYS_CONNECT_BY_PATH (ENAME, ','), ',') PATH#
FROM (SELECT ENAME
, DEPTNO
, ROW_NUMBER () OVER (PARTITION BY DEPTNO ORDER BY ENAME) RN
, COUNT (*) OVER (PARTITION BY DEPTNO) CNT
FROM EMP)
WHERE RN = CNT
START WITH RN = 1
CONNECT BY PRIOR RN = RN - 1
AND PRIOR DEPTNO = DEPTNO
- 참고사이트
ORA-30004 에러 발생시....대처방법]=================
col name format a50;
select job,substr(replace(sys_connect_by_path(ename, ',' ),' ',''),2) name
from(
select job,ename,
row_number() over(partition by job order by job,ename desc) r1,
(LEAD(ename) over(partition by job order by job,ename desc)) r2
from scott.emp
)
where r1 = 1
start with r2 is null
connect by prior ename = r2
order siblings by r2;