How to select values from many rows that has the same id to just one row and separate them with -?

Question

i'd like to display the values of the table with the same id in just one row and separate them with - or,

 row id | base_id | auth_id
--------+---------+---------
      4 |       1 |       1
      5 |       1 |       3
      6 |       2 |       2
      7 |       2 |       6
      8 |       2 |       5

result i expect

 row id | base_id | auth_id
--------+---------+---------
      1 |       1 | 1-3
      2 |       2 | 2-6-5

Are you using MySQL or Postgres? They are different databases. — Tim Biegeleisen, Jul 31 '19 at 05:00
Are you **really** still using Postgres 9.1? In that case, you should plan the upgrade to the current (supported) version now. — , Jul 31 '19 at 06:41

Tim Biegeleisen · Answer 1 · 2019-07-31T05:09:30.773

1

Try this option using string_agg:

select
    row_number() over (order by base_id) row_id,
    base_id,
    string_agg(auth_id, '-' order by auth_id)
from your_table
group by
    base_id
order by
    base_id;

edited Jul 31 '19 at 05:09

answered Jul 31 '19 at 04:57

Tim Biegeleisen

502,043
27
286
360

Barbaros Özhan · Accepted Answer · 2019-07-31T05:21:46.773

1

You can use string_agg() concatenation and row_number() window analytic function :

select row_number() over (order by base_id) as row_id, 
       base_id, string_agg(auth_id::varchar,'-' order by auth_id) as auth_id
  from tab
 group by base_id;

Demo

edited Jul 31 '19 at 05:21

answered Jul 31 '19 at 05:10

Barbaros Özhan

59,113
10
31
55

the compiler says "the function string_agg() doesn't exist" – Reza Pratama Jul 31 '19 at 05:13
@RezaPratama yes, casting to varchar needed. – Barbaros Özhan Jul 31 '19 at 05:22

How to select values from many rows that has the same id to just one row and separate them with -?

2 Answers2