Work/PostgreSQL
열->줄 변환
Elick
2011. 11. 8. 19:39
개요.
정규화 되어 있는 Data를 컴마(,) 혹은 특정 구분자로 이루어진 비정규형 Data로 표현하는 방법에 대한 DBMS별 Solution.Example.
CREATE TABLE T_TEST (ID_KIPI varchar2(32), INVENTOR varchar2(64));
INSERT INTO T_TEST VALUES ('EP000000004A1_19781220', '박상가');
INSERT INTO T_TEST VALUES ('EP000000004A1_19781220', '박상나');
INSERT INTO T_TEST VALUES ('EP000000004A1_19781220', '박상다');
INSERT INTO T_TEST VALUES ('EP000000004A1_19781220', '박상라');
Solution 1. Oracle (10g)
SELECT ID_KIPI, WM_CONCAT(INVENTOR)
FROM T_TEST
GROUP BY ID_KIPI;
-- 11g에서는 listagg도 지원한다.
Solution 2. SQL Server
SELECT distinct
ID_KIPI
,STUFF(
(
SELECT ', ' + b.INVENTOR
FROM T_TEST b
where a.ID_KIPI = b.ID_KIPI
FOR XML PATH('')
),1, 2, ''
) AS INVENTOR
FROM T_TEST a
Solution 3-1. PostgreSQL (9.0 이하)
SELECT ID_KIPI, array_to_string(array_agg(INVENTOR),',')
FROM T_TEST
GROUP BY ID_KIPI;
Solution 3-2. PostgreSQL (9.1 이상)
SELECT ID_KIPI, string_agg(INVENTOR,',' order by INVENTOR)
FROM T_TEST
GROUP BY ID_KIPI;