subselect - Resultset in a comma separated row

[galaxy0815]

Hello,

Please be advised that I have a complex SQL Statement that joins various tables. Within this statement I want to do the following (not sure if this is possible):

As 3rd field in the select part of that complex SQL statement, I want to enter a subselect that runs over a table called "car_availablility" which has the following layout:

Table car_availablility layout
Car -------- Owner -------------Currently_available
BMW -------- Dirk -------------- YES
VW --------- Dirk -------------- YES
Skoda ------ Dirk -------------- YES
Ford ------- Dirk -------------- NO
Seat ------- Marco ------------- YES
VW --------- Marco ------------- NO
Opel ------- Marco ------------- YES

the subselect looks as follows (with a link to the a.Owner field from the main complex select statement)
(select Car from car_availablility where a.Owner = car_availablility.Owner and Currently_available = 'YES') as Car_Overview


The issue is now the following:
Instead of having the result from the subselect shown on multiple lines, I want to list the values behind each other comma separated within the same field.

Example:
Instead of having the cars for Dirk listed as this:
Car_Overview
BMW
VW
Skoda

I want to have them listed as
Car_Overview
BMW, VW, Skoda


Is this possible just within a select statement (I cannot use stored procedures etc as my user just has select privileges)

Systeminfo:
Database server = DB2/NT64 9.5.8


Thanks a lot in advance!
Regards,

 

[delta403]

LOOKS LIKE NOT POSSIBLE BY USING SQL; PLEASE LET ME KNOW, IF YOU HAVE SOLUTION. THANKS.

 

[galaxy0815]

Hi,

Still trying on my side, but maybe try with one of the below:

SELECT replace(replace(XMLSERIALIZE(CONTENT xmlagg(xmlelement(Name a, CAR)) AS VARCHAR(32599)),'<A>',''),'</A>','; ')
FROM car_availablility

or

SELECT replace(replace(xml2clob(xmlagg(xmlelement(NAME a, car))),'<A>',''),'</A>',' ')
FROM car_availablility

They should work, but not sure if this is the best way.


Regards,