INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

subselect - Resultset in a comma separated row

subselect - Resultset in a comma separated row

(OP)
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,

RE: subselect - Resultset in a comma separated row

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

RE: subselect - Resultset in a comma separated row

(OP)
Hi,

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

CODE

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

or

CODE

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,

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close