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!

score 7 · Accepted Answer · answered Jul 09 '18 at 16:15

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 09 '18 at 16:15

Kaushik Nayak

30,772
5
32
45

score 2 · Answer 2 · answered Jul 09 '18 at 16:14

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 09 '18 at 16:14

max taldykin

12,459
5
45
64

`string_agg()` is the better solution – Jul 09 '18 at 16:41

From multiple rows to single column in Postgres

2 Answers2

Related