Oracle XMLAGG去重

CREATE TABLE AGGTEST(NAME VARCHAR2(10),TYP VARCHAR2(10));

SELECT T.* FROM AGGTEST T;
NAME	TYP
alley	GCGC
jacky	GCGC
pr	ICGC
candy	GCGC
dc	ICGC
alley	GCGC

SELECT XMLAGG(XMLPARSE( CONTENT T.NAME||';' WELLFORMED) ORDER BY T.TYP).GETCLOBVAL() AS NAME_ALL,T.TYP
  FROM (SELECT NAME,TYP,ROW_NUMBER() OVER(PARTITION BY TYP,NAME ORDER BY NAME) AS SEQ
          FROM AGGTEST T1
        )T 
 WHERE SEQ = 1
  GROUP BY T.TYP;
 NAME_ALL               TYP
 alley;jacky;candy;     GCGC
 dc;pr;                 ICGC
 
SELECT XMLAGG(XMLELEMENT(E,T.NAME,';').EXTRACT('//text()')).GETCLOBVAL() AS NAME_ALL,T.TYP
  FROM AGGTEST T
 GROUP BY T.TYP;

  

posted @ 2023-05-23 15:42  Ayumie  阅读(882)  评论(0编辑  收藏  举报