CODEWITHSUNDEEP
sqloracle-database
JoeS.
JoeS.

Reputation: 73

SQL Query With Multiple Fields Rolled Into One Record

Say I have the following query:

SELECT     KEY,
           BILL,
           CODE      
FROM       TABLE1
INNER JOIN TABLE2 ON TABLE1.KEY = TABLE2.KEY
ORDER      TABLE1.KEY;

And this outputs the following Table

KEY  |  BILL |  CODE
1234 |  AAA  |  ABC
1234 |  AAA  |  BCD
1234 |  AAA  |  DEF

Is there a way to query to return the record as: 1234 AAA ABC AAA BCD AAA DEF? I basically want to roll up every output for each record that has the same key. This is in Oracle.

Upvotes: 2

Views: 73

Answers (1)

Grisha Weintraub
Grisha Weintraub

Reputation: 7986

You can do it with listagg :

SELECT KEY,
       LISTAGG(BILL||' '||CODE, ' ') WITHIN GROUP (ORDER BY BILL||' '||CODE)     
   FROM TABLE1
   INNER JOIN TABLE2
   ON TABLE1.KEY = TABLE2.KEY
   ORDER TABLE1.KEY
GROUP BY KEY

Upvotes: 3

Related Questions