Search code examples
sqloracleoracle11gstring-aggregation

Oracle multple rows to single row

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?

Solution

  • 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;

    screen capture of demo below

    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.