Oracle XMLAGG去重

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

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;