From multiple rows to single column in Postgres

Question

I am using Postgres 9.5 via pgAdmin 4 with read only access and im trying to write a select query that converts data from this form:

+----------+-------------------+--------------------+----------------+
| username |      filters      |       groups       |     roles      |
+----------+-------------------+--------------------+----------------+
| kd24     | Khaled  <27607>   | V1                 | NewsStand User |
| kd24     | Khaled  <27607>   | V1                 | User           |
| kd24     | Khaled  <27607>   | Weekly KPIs        | NewsStand User |
| kd24     | Khaled  <27607>   | Detailed Sales     | User           |
| kd24     | Khaled  <27607>   | Qanz Monthly Group | User           |
| kd24     | Khaled  <27607>   | Detailed Sales     | NewsStand User |
| kd24     | Khaled  <27607>   | Qanz Monthly Group | NewsStand User |
| kd24     | Khaled  <27607>   | Weekly KPIs        | User           |
| kd24     | Khaled  <F_27607> | Weekly KPIs        | User           |
| kd24     | Khaled  <F_27607> | Weekly KPIs        | NewsStand User |
| kd24     | Khaled  <F_27607> | Qanz Monthly Group | NewsStand User |
| kd24     | Khaled  <F_27607> | Detailed Sales     | User           |
| kd24     | Khaled  <F_27607> | V1                 | User           |
| kd24     | Khaled  <F_27607> | Detailed Sales     | NewsStand User |
| kd24     | Khaled  <F_27607> | Qanz Monthly Group | User           |
| kd24     | Khaled  <F_27607> | V1                 | NewsStand User |
| kd24     | khaled.d          | Weekly KPIs        | User           |
| kd24     | khaled.d          | V1                 | NewsStand User |
| kd24     | khaled.d          | Detailed Sales     | NewsStand User |
| kd24     | khaled.d          | Qanz Monthly Group | NewsStand User |
| kd24     | khaled.d          | Weekly KPIs        | NewsStand User |
| kd24     | khaled.d          | V1                 | User           |
| kd24     | khaled.d          | Detailed Sales     | User           |
| kd24     | khaled.d          | Qanz Monthly Group | User           |
+----------+-------------------+--------------------+----------------+

To this form:

+----------+-----------------------------------------------+-----------------------------------------------------+---------------------+
| username |                    filters                    |                       groups                        |        roles        |
+----------+-----------------------------------------------+-----------------------------------------------------+---------------------+
| kd24     |  Khaled  <27607>, Khaled  <F_27607>,khaled.d  | V1, Weekly KPIs, Detailed Sales, Qanz Monthly Group | NewStand User, User |
+----------+-----------------------------------------------+-----------------------------------------------------+---------------------+

Noting that values in columns filters, groups and roles might change.

Is the a postgres select script that can perform that?

Much appreciated!

you are looking for string_agg()

user330315
– user330315

2018-07-09 16:14:13 +00:00
Commented Jul 9, 2018 at 16:14 — user330315
– user330315, Commented Jul 9, 2018 at 16:14

Kaushik Nayak · Accepted Answer · 2018-07-09 16:15:27Z

7

Try this

SELECT
    username,
    string_agg(DISTINCT filters,',') as filters,
    string_agg(DISTINCT groups,',') as roles,
    string_agg(DISTINCT roles,',') as groups
FROM
    table1
GROUP BY
    username;

Demo

answered Jul 9, 2018 at 16:15

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

Comments

max taldykin · Accepted Answer · 2018-07-09 16:14:48Z

2

You can use distinct keyword with aggregation function like array_agg to collect distinct values from each column:

select
    array_agg(distinct username) as username,
    array_agg(distinct filters) as filters,
    array_agg(distinct groups) as groups,
    array_agg(distinct roles) as groups
from YourTable;

answered Jul 9, 2018 at 16:14

max taldykin

13k5 gold badges49 silver badges69 bronze badges

1 Comment

user330315 Over a year ago

string_agg() is the better solution

Collectives™ on Stack Overflow

From multiple rows to single column in Postgres

2 Answers 2

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Linked

Related