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;
All for u

浙公网安备 33010602011771号