CODEWITHSUNDEEP
sqloracle-database
Jeanseb
Jeanseb

Reputation: 45

multiple result Rows in one row

I have this kind of result from a Select like this 

Select a.Header1, a.Header2, a.Header3, b.header4,
From a
Join b on a.id = b.id

give this result

Head1   Head2   Head3   Head4
1         A       1       AA
1         A       1       BB
1         A       1       CC
1         A       2       AA
1         A       2       CC
1         A       2       DD
1         A       2       EE

Is it possible to have 

Head1   Head2   Head3   Head4 
1         A       1       AA    BB    CC
1         A       2       AA          CC    DD    EE

Head4 being split in different columns

Upvotes: 0

Views: 69

Answers (1)

ohmu
ohmu

Reputation: 19760

Not exactly. You can use the listagg() function to concatenate the values into a single column which you can then split after the fact. Example:

SELECT
    a.Header1,
    a.Header2,
    a.Header3,
    listagg(b.Header4, ', ') WITHIN GROUP (ORDER BY b.Header4) AS Header4
FROM a
JOIN b ON a.id = b.id
GROUP BY
    a.Header1,
    a.Header2,
    a.Header3

Would yield:

Header1   Header2   Header3   Header4 
1         A         1         AA, BB, CC
1         A         2         AA, CC, DD, EE

Upvotes: 2

Related Questions