Oracle multple rows to single row

Question

I want to concatenate multiple rows to a sigle row in oracle. Below is what I am trying to achieve

case table

case_id Name
1       AAA
2       BBB

Subject table

case_id Subject
1       ENG
1       MATH
1       SCI
2       ENG
2       HIS

SUB_LOOKUP table

Abbr    Subject
ENG     English
MATH    Mathematics
SCI     Science
HIS     History

Expected Output

1   AAA     English, Mathematics, Science
2   BBB     English, History

I tried using LISTAGG function as below. It lists what I would want to achieve but the issue comes when I join with the lookup table and then join to main table.

SELECT c.case_id, c.category_id, LISTAGG(cs.special_interest_cd) WITHIN GROUP 
(ORDER BY cs.special_interest_cd) AS description
FROM CASE_SPECIAL_INTEREST cs, cases c
where c.case_id = cs.case_id
GROUP BY c.case_id;

Tried joining 2 tables but getting "ORA-00979: not a GROUP BY expression" Can someone help with joining the 3 tables. Is it possible to achieve the same without LISTAGG since i read it throws eception when the max character is reached for varchar?

Tim Biegeleisen · Accepted Answer · 2020-04-13 03:26:38Z

2

You only need a basic join between the three tables, followed by an aggregation by case_id and name:

SELECT
    c.case_id,
    c.Name,
    LISTAGG(sl.subject, ',') WITHIN GROUP (ORDER BY sl.subject) AS subject
FROM "case" c
LEFT JOIN subject s
    ON c.case_id = s.case_id
LEFT JOIN sub_lookup sl
    ON s.Subject = sl.Abbr
GROUP BY
    c.case_id,
    c.Name;

Demo

Note that I use left joins here, to ensure that if a given case_id has no matches for the aggregation, that record/value would still be reported in the result set.

edited Apr 13, 2020 at 3:26

answered Apr 13, 2020 at 3:18

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Geek Over a year ago

thank you. When I run this query independently it works great and gives me result exactly like what i want. But I have to add this query inside an already exisiting query which is really huge and the result is 1 AAA Mathematics. I cant paste the original query here since its like 500 lines of code but any suggestions?

Tim Biegeleisen Over a year ago

No, I can't make a suggestion without seeing the actual query.

Collectives™ on Stack Overflow

Oracle multple rows to single row

1 Answer 1

Demo

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related