GROUP 콤마구분자로 연결하기

환경 : 오라클 12C

 

 

SELECT LISTAGG( GROUP_KEY || '_V, ' || GROUP_KEY || '_R' , ', ') WITHIN GROUP (ORDER BY GROUP_KEY) V1

FROM (

SELECT 1 GROUP_KEY FROM DUAL UNION ALL

SELECT 2 GROUP_KEY FROM DUAL UNION ALL

SELECT 3 GROUP_KEY FROM DUAL UNION ALL

SELECT 4 GROUP_KEY FROM DUAL

) A;

 

결과 : 1_V, 1_R, 2_V, 2_R, 3_V, 3_R, 4_V, 4_R

 

 

반대로

 

select TRIM(REGEXP_SUBSTR( COLS, '[^,]+', 1, 1, 'i')) COL1

,TRIM(REGEXP_SUBSTR( COLS, '[^,]+', 1, 2, 'i')) COL2

,TRIM(REGEXP_SUBSTR( COLS, '[^,]+', 1, 3, 'i')) COL3

,TRIM(REGEXP_SUBSTR( COLS, '[^,]+', 1, 4, 'i')) COL4

from

(

SELECT 'A,B,C,D' COLS

FROM DUAL

) a

;