꿈꾸는 개발자, DBA 커뮤니티 구루비

Group By 에서 문자열 합치기 - 버전별 정리

by 마농 [마농] LISTAGG WM_CONCAT XMLAGG [2012.03.19 08:48:47]

WITH t AS
(
SELECT '과일' type, '사과' name, '0' code FROM dual
UNION ALL SELECT '과일', '레몬', '1' FROM dual
UNION ALL SELECT '과일', '포도', '2' FROM dual
UNION ALL SELECT '과일', '참외', '3' FROM dual
UNION ALL SELECT '채소', '오이', '0' FROM dual
UNION ALL SELECT '채소', '당근', '1' FROM dual
UNION ALL SELECT '채소', '호박', '2' FROM dual
)
SELECT type
     , SUBSTR(XMLAgg(XMLElement(x, ',', name) ORDER BY code).Extract('//text()'), 2) name_9i
     , wm_concat(name) name_10g
     , ListAgg(name, ',') WITHIN GROUP(ORDER BY code) name_11g
  FROM t
 GROUP BY type
 ORDER BY type
;

<실행 결과>

TYPE	NAME_AGG
과일	사과,레몬,포도,참외
채소	오이,당근,호박

by 타락천사 [2012.03.19 08:51:57]

스크랩 !!!!

by 손님 [2012.03.23 14:23:31]

저두 스크랩해가요 .. 감사합니다. 마농님

by 김용한 [2012.04.04 09:49:58]

아 이걸 표준쿼리로 한다면 어찌될까요? ....

by 손님 [2012.08.07 11:44:50]

감사합니다..

그런데요..

WITH t(type, name, code) AS 에서 지원되지 않는 열 별칭 지정 에러가 나는데요..

With문에도 오라클 버젼특성을 타나요??

오라클버젼 10.0.2.4 입니다..

by 손님 [2012.08.26 13:14:16]

10g 버전에서는 with 절의 쿼리 블럭에 컬럼 별칭을 넣을 수가 없습니다.
아래와 같이 고쳐 쓰세요.

WITH t AS
(
SELECT '과일' AS type, '사과' AS name, '0' AS code FROM dual
-- 이하 마농님 코드와 동일합니다.

by 손님 [2012.08.26 13:18:43]

참, 10g라면 당연히 ListAgg 함수는 빼고 사용하셔야 합니다.

by 진 [2012.09.25 12:13:59]

오오~ 잘 보고 갑니다~

by 쬐끔만더 [2012.11.29 22:34:30]

정말 유용하게 쓸거 같네요..감사합니다.

by 마농 [2013.06.04 10:46:58]

Group By 에서 문자열 합치기 - DBMS 별 정리
 
-- MySQL | MariaDB | CUBRID --
SELECT type
     , Group_Concat(name ORDER BY code) name_MySQL
  FROM t
 GROUP BY type
 ORDER BY type
;

-- MSSQL --
SELECT type
     , STUFF((SELECT ',' + name
                FROM t
               WHERE type = a.type
               ORDER BY code
                 FOR XML PATH('')
              ), 1, 1, '') name_MSSQL_old
     , String_Agg(name, ',') WITHIN GROUP(ORDER BY code) name_MSSQL_2017
  FROM t a
 GROUP BY type
 ORDER BY type
;

-- Tibero --
SELECT type
     , Aggr_Concat(name, ',' ORDER BY code) name_Tibero
  FROM t
 GROUP BY type
 ORDER BY type
;

-- Sybase --
SELECT type
     , List(name, ',' ORDER BY code) name_Sybase
  FROM t
 GROUP BY type
 ORDER BY type
;

-- PostgreSQL --
SELECT type
     , Array_to_String(Array_Agg(name ORDER BY code), ',') name_PostgreSQL
  FROM t
 GROUP BY type
 ORDER BY type
;

by 초록짱 [2013.06.07 17:29:52]

감사합니다.
제가 테스트 한 버젼은 11.1.0.6 인데 지원되지 않는 열 별칭 지정 에러가 뜨네여.
11g 초기버젼에는 ListAgg 지원이 안되는 건가요...

by 윈짱천사 [2013.06.21 16:35:44]

좋은정보 감사합니다. ^^*
필요할때마가 검색했는데..
스크랩 합니다.

by 손님 [2013.08.12 10:58:30]

좋은정보 감사합니다.~
출처남기고 스크랩해갈게요~^^

by 홍상표 [2014.03.31 16:07:28]

대단하시네용.. 스크랩 해갑니다..

by 박민철 [2014.08.21 16:34:36]

유요한 정보 감사합니다.

by 마농 [2015.01.08 18:41:11]

-- 오류 : ORA-01489: 문자열 연결의 결과가 너무 깁니다.
-- 원인 : 4000 Byte 이상 문자 연결.
-- 해결 : XMLAgg 의 getCLOBval() 기능 이용.
-- 해결 : 12C 의 ON OVERFLOW TRUNCATE 기능 이용.
SELECT type
     , SUBSTR(
       XMLAgg(XMLElement(x, ',', name) ORDER BY code).Extract('//text()').getCLOBval()
       , 2) name_clob
     , ListAgg(name, ',' ON OVERFLOW TRUNCATE) WITHIN GROUP(ORDER BY code) name_12c
  FROM t
 GROUP BY type
 ORDER BY type
;