Group by in SQL with commas

Question

I'm using Oracle SQL and i have a question regarding group by command.

I have the following table:

Column_A (int)
Column_B (int)

Example for data in the table:

Column_A | Column_B
 11      |    2
 23      |    3
 32      |    4
 32      |    10
 11      |    23
 23      |    11
 44      |    1
 23      |    5

I want to Group by Column_A while the values of Column_b will be terminated by commas. Output table:

Column_A | Column_B
 11      |  2, 23
 23      |  3, 11
 32      |  4, 10, 5
 44      |  1

Any recommendation how to do that?

This is technically speaking not grouping but reducing. – Smutje Oct 16 '14 at 08:57 — Smutje, Oct 16 '14 at 08:57

score 2 · Accepted Answer · edited Oct 16 '14 at 09:04

2

Use function listagg

SELECT Column_A, listagg( Column_B, ',' ) WITHIN GROUP( order by Column_B)
  FROM table_name
 GROUP BY Column_A

edited Oct 16 '14 at 09:04

a_horse_with_no_name

497,550
91
775
843

answered Oct 16 '14 at 08:58

Barry

3,555
1
16
25

@a_horse_with_no_name I was sloppy again with the code formatting, thanks for the edit. – Barry Oct 16 '14 at 09:09
Thanks! That what i looked for. – Omri Oct 16 '14 at 09:09

score 2 · Answer 2 · answered Oct 16 '14 at 08:58

2

You can use LISTAGG:

SELECT column_A,
       LISTAGG(column_B, ', ') WITHIN GROUP (ORDER BY column_B) column_B
  FROM your_table
 GROUP
    BY column_A

answered Oct 16 '14 at 08:58

DirkNM

2,544
13
21

Thanks! That what i looked for. – Omri Oct 16 '14 at 09:10

Group by in SQL with commas

2 Answers2