CODEWITHSUNDEEP
sqloracle-database
vishal pathak
vishal pathak

Reputation: 13

Concate Data from multiple rows in one as concatenated with special char

I have a requirement to show the data in one row concatenated with special char. For example

CREATE TABLE vis_tab ( TTid varchar(40), site varchar(40), affected varchar(40));

insert into vis_tab(ttid, site, affected) values ('1', 's1', 'a1');
insert into vis_tab(ttid, site, affected) values ('1', 's1', 'a2');
insert into vis_tab(ttid, site, affected) values ('1', 's2', 'a22');
insert into vis_tab(ttid, site, affected) values ('1', 's2', 'a21');
insert into vis_tab(ttid, site, affected) values ('1', 's1', 'a24');

Desired output:

RESULT
-------------------------------------------
s1-a1,a2,a24#-#s2-a22,a21

On high level it should show SITE followed by "-" then all AFFECTED SERVICE. Then append the next row of the result with #-# (or any other) and so on.

Hope I am able to put my question in clear manner.

Upvotes: 1

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Confusing format. You want to use listagg() twice:

select listagg(site || '-' || affectes, '#') within group (order by site)
from (select t.site,
             listagg(affected, ',') within group (order by affected) as affectes
      from vis_tab t
      group by t.site
     ) t;

Upvotes: 1

Related Questions