LISTAGG([합칠 컬럼명], [구분자]) WITHIN GROUP(ORDER BY [정렬 컬럼명])
LISTAGG함수는 여러행으로 조회되는 것을 한 행으로 합쳐서 보여줄 때 용이하게 사용된다.
때문에 GROUP BY 또는 PARTITION BY 절과 함께 사용된다.
WITH TEST_DB AS (
SELECT '김철수'AS NM, '101'AS CROOM FROM DUAL
UNION
SELECT '김영수'AS NM, '102'AS CROOM FROM DUAL
UNION
SELECT '김동미'AS NM, '103'AS CROOM FROM DUAL
UNION
SELECT '김슬기'AS NM, '101'AS CROOM FROM DUAL
UNION
SELECT '김지영'AS NM, '102'AS CROOM FROM DUAL
)
SELECT * FROM TEST_DB;
WITH TEST_DB AS (
SELECT '김철수'AS NM, '101'AS CROOM FROM DUAL
UNION
SELECT '김영수'AS NM, '102'AS CROOM FROM DUAL
UNION
SELECT '김동미'AS NM, '103'AS CROOM FROM DUAL
UNION
SELECT '김슬기'AS NM, '101'AS CROOM FROM DUAL
UNION
SELECT '김지영'AS NM, '102'AS CROOM FROM DUAL
)
SELECT CROOM, LISTAGG(NM, ',') WITHIN GROUP(ORDER BY NM) AS NMS
FROM TEST_DB
GROUP BY CROOM;
납부 FLAG를 1 // 미납 FLAG를 3 // 부분납부 FLAG를 2 로 저장한 테이블이 있다고 가정하였다.
이런경우, 납부+부분납부한 경우를 조회하고 싶을때
INSTR 함수를 이용하여 조회할 수 있다.
WITH TEST_DB AS(
SELECT '1' AS FLAG, 'A101' AS 호수 FROM DUAL
UNION
SELECT '2' AS FLAG, 'A201' AS 호수 FROM DUAL
UNION
SELECT '3' AS FLAG, 'A301' AS 호수 FROM DUAL
UNION
SELECT '2' AS FLAG, 'A401' AS 호수 FROM DUAL
UNION
SELECT '3' AS FLAG, 'A501' AS 호수 FROM DUAL
)
SELECT * FROM TEST_DB WHERE INSTR('12', NVL(FLAG,0)) > 0;
