Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    15

    Unhappy Unanswered: Concatenating multiple rows into one field

    Hi everyone, I have the following query:
    SELECT DISTINCT engine_model
    FROM qs_engine_list,
    si_plants
    WHERE qs_engine_list.engine_family = si_plants.engine_family
    AND si_plants.doc_num = p_doc_num
    ORDER BY engine_model;

    Given a document number of 3810477 it might return 3 rows that
    have engine x, y, and z. But what I would really like it to return is
    one row that looks like "x, y, z".

    I know how to do this programatically but I need it to happen as part of the query.

    Does anyone know how to do this, or is it even possible?

  2. #2
    Join Date
    May 2003
    Posts
    87
    You would be better off by writing a common function which returns you the output string after passing the sql query as an input string.

    Here is a query to get the results but if you are expecting a large number of rows, might get longer. This one works for 5 rows.

    Code:
    select max(decode(rnbr, 1, emp_no, null)) || ',' || 
             max(decode(rnbr, 2, emp_no, null)) || ',' || 
             max(decode(rnbr, 3, emp_no, null)) || ',' ||
             max(decode(rnbr, 4, emp_no, null)) || ',' || 
             max(decode(rnbr, 5, emp_no, null)) emp_string
    from  ( select dept_no, emp_no, row_number() over (partition by dept_no order by dept_no) as rnbr
            from emp )
    group by dept_no
    /

  3. #3
    Join Date
    Jul 2003
    Posts
    15

    Thumbs up

    Yeah, I ultimately figured out use a function inside of the query and it
    worked great. Thanks for the help anyway, though.

Posting Permissions

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