Concatenating multiple rows into one field

brewerd · #1 (**permalink**) 10-23-03, 15:31

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?

dbmadcap · #2 (**permalink**) 10-23-03, 16:25

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
/

brewerd · #3 (**permalink**) 10-23-03, 17:05

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

Internet Services

Web Development

Digital Marketing

Consumer Tech