mysql xmlagg,DB2 Distinct + xmlagg查询

I want equivalent to GROUP_CONCAT functionality of MySql in DB2.

I have tried XML Aggrigate functon of DB2 for cocating murows.

SELECT a.ID,

substr(xmlserialize(xmlagg(xmltext( concat(',', SPECIALISATION)))as varchar( 1024 )),2),

substr(xmlserialize(xmlagg(xmltext(concat(',,, BASIC_SKILL2)))as varchar( 1024 )),2),

substr(xmlserialize(xmlagg(xmltext(concat(',', BASIC_SKILL1)))as varchar( 1024 )),2)

FROM candidate_resume_data a,candidate_skills_info b,skill_special_master c,skill_master_basic2 d,skill_master_basic1 e

WHERE e.SKILL_BASIC1_ID = d.SKILL_BASIC1_ID

AND b.ID = a.ID

AND d.SKILL_BASIC2_ID = c.SKILL_BASIC2_ID

AND b.CANDIDATE_SPECIALISATION_ID = c.SKILL_SPECIAL_ID

GROUP BY a.ID;

Which gives me result

ID | SPECIALISATION | BASIC_SKILL2 | BASIC_SKILL1 |

----+---------------------------------------------------------------------+

1 | Java,C++ | Development,Development | Software,Software |

But I want distinct/Unique Value of BASIC_SKILL2,BASIC_SKILL1.

ID | SPECIALISATION | BASIC_SKILL2 | BASIC_SKILL1 |

----+-------------------+-------------------+------------------+

1 | Java,C++ | Development | Software |

解决方案

The select distinct won't work in case of tables with no duplicates, because of multiple joins giving all the combinations of the values of every join. That leads to duplicates in the aggregate function.

I've found that pushing the group bys and the aggregate functions to subqueries in the from part gives the best results.

SELECT t.id, q1.values, q2.values, q3.values

FROM table_name t,

inner join (select t1.id, listagg(t1.value,',') as values

from table_name1 t1 inner join table_name t on t.id=t1.id

group by t1.id) q1 on t.id = q1.id

inner join (select t2.id, listagg(t2.value,',') as values

from table_name2 t2 inner join table_name t on t.id=t2.id

group by t2.id) q2 on t.id = q2.id

inner join (select t3.id, listagg(t3.value,',') as values

from table_name3 t3 inner join table_name t on t.id=t3.id

group by t3.id) q3 on t.id = q3.id