Group By 에서 문자열 합치기 - 버전별 정리 48 19 38,219

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
;

 

 


by 파이팅마모루 [2015.07.02 09:31:53]

유용한 정보 감사드립니다.

10g XE 버전에서는 WM_CONCAT이 지원되지 않는군요. ㅜㅜ


by 우리집아찌 [2016.05.03 01:16:49]

즐겨찾기 추가~~


by jkson [2017.08.24 18:44:35]

12C -> JSON_ARRAYAGG 함수 활용. 이 함수를 사용해도 4000 byte 이상 처리 가능할듯요.(returning clob 옵션) 12C 깔려있는 분들은 테스트해보세요.

https://docs.oracle.com/database/122/SQLRF/JSON_ARRAYAGG.htm#SQLRF-GUID-6D56077D-78DE-4CC0-9498-225DDC42E054

 

 


by 우리집아찌 [2017.09.27 21:14:36]

//jkson 

개발자에게 완전 유용할듯 JSON 데이터로 변환하는게 완전 편하겠는데

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입