Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: SQL: converting column data to one row - invert - pivot

    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
    Last edited by grofaty; 12-19-03 at 04:02.

  2. #2
    Join Date
    Jul 2003
    Location
    Austin, TX, USA
    Posts
    278

    I assume ID is primary key

    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
    Last edited by nitingm; 12-19-03 at 06:20.
    HTH

    Nitin

    Ask the experienced rather than the learned

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    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

  4. #4
    Join Date
    Apr 2003
    Posts
    191
    Hi Grofaty,

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

    Johann

    Originally posted by grofaty
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •