DB2 SQL select

[maggo119]

Hi I have a table which looks like this:

Nr Name1 Name2
== ===== =====
1--John---------
1----------Smith
2--Ricky--------
2----------Fred
2----------Mason
3--Steve--------


The result shoudl look like this:

Nr Name1 Name2 Name3
== ===== ===== =====
1 John Smith
2 Ricky Fred Mason
3 Steve

I already tried to use a temprary global table
and reverse query but I wasn't able to solve this problem.
I think it could be possible to solve it with reverse query,
if someone has an idea how to solve, please let me know.

Thanks a lot.

[Rabbit]

You can use the XML functions to aggregate the two fields, then append the aggregations together.

An example follows using the XML functions to aggregate a field

replace(replace(xml2clob(xmlagg(xmlelement(NAME a, fieldName)))),'<A>',', '),'</A>','')

[maggo119]

Thanks for your reply, I already solved by using 'Case When' statements.