죄송합니다, 중복제거 부탁드립니다 0 7 2,168

by 일곱난장이 [SQL Query] [2023.03.24 10:55:53]


select 

        listagg(a.BANG, ',') within group(order by a.BANG) as BANG_NM

  from ( select 'C01,C02,D01' as BANG from dual

           union all

           select 'C01,C02' as BANG from dual

           union all

            select 'A01,A03' as BANG from dual

       ) a

==> 결과 A01,A03,C01,C02,C01,C02,D01

==> 중복제거 

      A01,A03,C01,C02,D01 

  부탁드립니다

 

 

 

         

by 우리집아찌 [2023.03.24 11:01:06]

dbms는 무엇인가요? 오라클? mysql? mssq?

정렬이 보장되어야하나요?


by 일곱난장이 [2023.03.24 12:20:21]

오라클이고, 정렬이 되어야 합니다~

감사합니다~


by 우리집아찌 [2023.03.24 13:50:45]

-- 손으로 옮겨 적어서 오타 있을 수 있어요. 
WITH T AS ( 
select 'C01,C02,D01' as BANG from dual union all 
select 'C01,C02' as BANG from dual union all 
select 'A01,A03' as BANG from dual 

-- 19C 
SELECT LISTAGG(DISTINCT REGEXP_SUBSTR(BANG,'[^,]+',1,LV ) , ',') WITHIN GROUP( ORDER BY REGEXP_SUBSTR(BANG,'[^,]+',1,LV ) ) 
  FROM T A 
     ,(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= (SELECT MAX(REGEXP_COUNT(BANG,',')) FROM T ) + 1 ) B 
WHERE LV <= REGEXP_COUNT(A.BANG,',') + 1 

-- 19C 이전 버젼 
SELECT LISTAGG(V , ',') WITHIN GROUP( ORDER BY V ) 
  FROM (SELECT DISTINCT REGEXP_SUBSTR(BANG,'[^,]+',1,LV ) V 
          FROM T A 
             ,(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= (SELECT MAX(REGEXP_COUNT(BANG,',')) FROM T ) + 1 ) B 
         WHERE LV <= REGEXP_COUNT(A.BANG,',') + 1


by 일곱난장이 [2023.03.27 08:07:56]

감사합니다


by 동동동 [2023.03.24 16:17:42]

XMLTABLE 함수를 사용한 방법입니다.

WITH TMP AS ( 
    SELECT 'Z01,B01' AS BANG FROM DUAL UNION ALL
    SELECT 'C01,C02,D01' AS BANG FROM DUAL UNION ALL 
    SELECT 'C01,C02' AS BANG FROM DUAL UNION ALL 
    SELECT 'A01,A03' AS BANG FROM DUAL 
) 
SELECT LISTAGG(DISTINCT trim(column_value)) WITHIN GROUP(ORDER BY trim(column_value)) AS BANG_NM
FROM TMP, xmltable(('"' || REPLACE(BANG, ',', '","') || '"'));

XMLTABLE 함수를 사용하여 각 BANG 값을 컴마(,)로 분리하고, DISTINCT 키워드를 사용하여 중복된 값을 제거합니다. 그리고 ORDER BY 절을 사용하여 결과 집합을 정렬합니다.

 

[19C이하]

WITH TMP AS ( 
    SELECT 'Z01,B01' AS BANG FROM DUAL UNION ALL
    SELECT 'C01,C02,D01' AS BANG FROM DUAL UNION ALL 
    SELECT 'C01,C02' AS BANG FROM DUAL UNION ALL 
    SELECT 'A01,A03' AS BANG FROM DUAL 
) 
SELECT LISTAGG(BANG, ',') WITHIN GROUP(ORDER BY BANG) AS BANG_NM
FROM (
    SELECT DISTINCT TRIM(COLUMN_VALUE) AS BANG
    FROM TMP, XMLTABLE(('"' || REPLACE(BANG, ',', '","') || '"'))
);

 


by 우리집아찌 [2023.03.24 17:22:57]

XMLTABLE 쓰니까 간단하네요.


by 일곱난장이 [2023.03.27 08:07:18]

잘됩니다, 너무 감사합니다~~~~

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