CODEWITHSUNDEEP
sqlpostgresqlstring-aggregation
Dabbous
Dabbous

Reputation: 167

From multiple rows to single column in Postgres

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!

Upvotes: 4

Views: 8944

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

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

Upvotes: 7

max taldykin
max taldykin

Reputation: 12898

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;

Upvotes: 2

Related Questions