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

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
– Tim Biegeleisen, Commented Jul 31, 2019 at 5:00
Are you really still using Postgres 9.1? In that case, you should plan the upgrade to the current (supported) version now. — user330315
– user330315, Commented Jul 31, 2019 at 6:41

Barbaros Özhan · Accepted Answer · 2019-07-31 05:21:46Z

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, 2019 at 5:21

answered Jul 31, 2019 at 5:10

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Reza Pratama Over a year ago

the compiler says "the function string_agg() doesn't exist"

Barbaros Özhan Over a year ago

@RezaPratama yes, casting to varchar needed.

Tim Biegeleisen · Accepted Answer · 2019-07-31 05:09:30Z

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;

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related