How to get column values in one comma separated values

Question

I have a table which contains rows like below

ASETTYPEID  ASETTYPE
1           SYSTEM
2           PRINTER
3           PROJECTOR
4           SCANNER
5           AUDIO VISUAL AIDS
6           TAB
7           LAPTOPS
8           ALL IN ONE PC
9           OTHERS

I need a select query and inserting a comma separated values in new table which results following format

ASETTYPEID  ASETTYPE
1,2,3,4,5,7,8,9 SYSTEM,PRINTER,PROJECTOR,SCANNER,AUDIO VISUAL 
            AIDS,TAB,LAPTOPS,ALL IN ONE PC,OTHERS
1,3,5,8        SYSTEM,PRINTER,PROJECTOR

Concatenate the rows into CSV for display purposes only. Do not store/insert them into columns of a table.It's bound to cause you trouble forever. — Kaushik Nayak, Oct 17 '19 at 03:58

score 0 · Answer 1 · answered Oct 17 '19 at 04:01

Use listagg:

with tbl as
(SELECT 1 ASETTYPEID,   'SYSTEM' ASETTYPE from dual
UNION ALL 
select 2, 'PRINTER' from dual 
union all 
select 3, 'PROJECTOR' from dual
union all
select 4, 'SCANNER' from dual
union all
select 5, 'AUDIO VISUAL AIDS' from dual
union all
select 6, 'TAB' from dual
union all
select 7, 'LAPTOPS' from dual
union all
select 8, 'ALL IN ONE PC' from dual
union all
select 9, 'OTHERS' from dual)
SELECT listagg(ASETTYPEID, ', ') 
    WITHIN GROUP(order by ASETTYPEID) as ASETTYPEID_list,
    listagg(ASETTYPE, ', ') 
    WITHIN GROUP(order by ASETTYPEID) as ASETTYPE_list
FROM tbl
where  ASETTYPEID in (1,2,3,4,5)

output :

1, 2, 3, 4, 5   SYSTEM, PRINTER, PROJECTOR, SCANNER, AUDIO VISUAL AIDS

How to get column values in one comma separated values

1 Answers1