CODEWITHSUNDEEP
sqloracle-databaseconcatenationlistagg
Country girl
Country girl

Reputation: 45

Concatenate values from multiple rows from same field, aggregating

I have a dataset with names in one columns along with several other columns. I would like to show where all other values in the fields are the same, concatenate the names.

e.g. 

 col 1 col 2 col 3 
    a      1      mary 
    a      1      jane
    a      1      kevin
    b      2      mary
    b      2      jane
    b      2      kevin
    c      3      mary
    c      3      jane
    c      3      kevin

output to be:

a      1      mary, jane, kevin
b      2      mary, jane, kevin
c      3      mary, jane, kevin

I have tried using rtrim but it is not doing anything. I also tried using listagg but I get an error 'Not a group by expression'

All fields are strings and not calculable.

TIA

Upvotes: 0

Views: 143

Answers (1)

JohnHC
JohnHC

Reputation: 11195

Details here

select col1, 
       col2, 
       listagg(col3,     -- The aggregated column
               ',')      -- The delimiter
         within group    -- Because this is an aggregated function, needs to be grouped
         (order by Col3) -- We can order the aggregated values
           as Col3       -- And an alias for good measure
from TableA
group by col1, col2

Upvotes: 3

Related Questions