Hi,

I have table JOBS. I would like to write SQL to get all column data into one row separated with commas.

Sample:

Source data:
JOBID
-----
1
2
3
4
5
...
n


Result:
ALL_JOBS
--------
1,2,3,4,5,...,n

How to write such SELECT clause?

Thanks,
Grofaty

HI Grofaty.

Can you please try this

db2 select a.job_id concat ',' concat z.job_id as all_jobs from (select job_id from t4 fetch first 1 row only) as a, (select job_id from (select job_id from t4 fetch first 2 rows only) as a except select * from (select job_id from t4 fetch first 1 row only) as b) as z

HTH

Nitin.

OOPS I FORGOT THIS WORKS FOR 2 ROWS U NEED TO MODIFY THE SAME FOR MANY ROWS.

TABLE

JOB_ID

------

1

2

2 record(s) selected.

OUTPUT

1

---

1,2

Hi,

Sorry this does not work. I just forgot to mention that I have db2 version 7.2 for Windows 2000. In DB2 v7.2 does not work the "fetch first n rows only" in subselect. This is a new feature in DB2 v8.

Any other idea?

Thanks,
Grofaty

Hi Grofaty,

this won't work in general unless you consider common table expressions, which probably are no standard SQL, though.

Johann