CODEWITHSUNDEEP
sqloracle-database
old_soul_on_the_run
old_soul_on_the_run

Reputation: 289

How to join results of two sql queries?

I am working on a project and need some help. In order to frame the question well, I have created sample data and desired result format. I have a Table with three columns. All three rows can have duplicate data but the combination of CodeA, CodeB, CodeC is Unique. I need to have a Oracle SQL query (not a stored procedure) so that I can generate comma separated data in the following format on right hand side. The values in the comma separated rows needs to be distinct. Also, the Field 1 should be in sorted order.

enter image description here

The query I created is here but the problem with below is only that this is not getting me DISTINCT values. For example, at row 1 of output set, I am getting A1 B1,B1,B2,B3 C1,C1,C2,C2 which is what I don't want. Can anybody help me? 

SELECT CodeA As "Field 1",
 LISTAGG(CodeB, ',') WITHIN GROUP (ORDER BY CodeA) AS "Field 2",
 LISTAGG(CodeC, ',') WITHIN GROUP (ORDER BY CodeA) AS "Field 3"
FROM Table
GROUP BY CodeA
UNION
SELECT CodeB As "Field 1",
 LISTAGG(CodeA , ',') WITHIN GROUP (ORDER BY CodeB) AS "Field 2",
 LISTAGG(Code3, ',') WITHIN GROUP (ORDER BY CodeB) AS "Field 3"
FROM Table
GROUP BY CodeB;

Upvotes: 0

Views: 62

Answers (1)

Brian DeMilia
Brian DeMilia

Reputation: 13248

Try:

select x.codea, x.codeb, y.codec
from
(
  select codea, listagg(codeb,',') within group (order by codeb) as codeb
  from (select distinct codea, codeb from tbl)
  group by codea
) x
join
(
  select codea, listagg(codec,',') within group (order by codec) as codec
  from (select distinct codea, codec from tbl)
  group by codea
) y
  on x.codea = y.codea

Upvotes: 1

Related Questions