how to unnest multiple arrays and transpose table in postgres 9.5

Question

Lets suppose i have this table:

project  |leader|coordination|staff              |support    |
---------|------|------------|-------------------|-----------|
project_a|Bob   |{Alice}     |{Terry,Mandy,James}|{Bob,Peter}|
project_b|Alice |{Terry, Bob}|{Mandy,James}      |{Maggie}   |
project_c|Maggie|{Bob}       |{Terry,Peter}      |{Alice,Bob}|

and i want to transform it to something like this:

person|project_1|role_1|project_2|role_2      |project_3|role_3 |project_4|role_4      |
------|---------|------|---------|------------|---------|-------|---------|------------|
Alice |project_b|leader|project_a|coordination|project_c|support|         |            |
Bob   |project_a|leader|project_a|support     |project_b|staff  |project_c|coordination|
Terry...
Maggie...
...

Big thanks to @abelisto for pointing me in the right direction in the comments.

The problem with crosstab is, that i need the project also in the row:

SELECT *
FROM   crosstab(
   '
select
    person,
    project,
    role
from
    tmp_projects
cross join lateral (
    select
        \'leader\'::text as role, leader as person
union all
    select
        \'coordination\', x
    from
        unnest(coordination) as x
union all
    select
        \'staff\', x
    from
        unnest(staff) as x
union all
    select
        \'support\', x
    from
        unnest(support) as x) as t order by 1,2'  -- needs to be "ORDER BY 1,2" here
  , 'SELECT DISTINCT project FROM tmp_projects ORDER BY 1'
   ) AS ct ("Person" text, "Project_1" text,"Project_2" text,"Project_3" text);

The problem with Terry, Bob in one line is because of the white space in the array... — derhelge
– derhelge, Commented Jan 24, 2021 at 16:48
Try select project, person, role from tmp_projects cross join lateral (select 'leader'::text as role, leader as person union all select 'coordination', x from unnest(coordination) as x union all select 'staff', x from unnest(staff) as x union all select 'support', x from unnest(support) as x) as t; Then you can to pivot the data. — Abelisto
– Abelisto, Commented Jan 24, 2021 at 17:03
Mhh... With crosstab its already looking good, but i also need the "project" in the row. — derhelge
– derhelge, Commented Jan 24, 2021 at 19:20

derhelge · Accepted Answer · 2021-01-24 19:37:44Z

@Abelisto had the correct idea and a solution could be:

create view tmp_view_projects_unnest as select
    person,
    row_number() OVER (PARTITION by person) AS rn,
    project,
    role
from
    tmp_projects
cross join lateral (
    select
        'leader'::text as role, leader as person
union all
    select
        'coordination', x
    from
        unnest(coordination) as x
union all
    select
        'staff', x
    from
        unnest(staff) as x
union all
    select
        'support', x
    from
        unnest(support) as x) as t order by 1,2

person|rn|project  |role        |
------|--|---------|------------|
Alice | 1|project_a|leader      |
Alice | 2|project_d|coordination|
Alice | 3|project_c|support     |
Bob   | 1|project_d|coordination|
Bob   | 2|project_c|coordination|
Bob   | 3|project_c|support     |
Maggie| 1|project_c|leader      |
Mandy | 1|project_d|leader      |
Marry | 1|project_d|staff       |
Peter | 1|project_d|support     |
Peter | 2|project_c|staff       |
Peter | 3|project_a|coordination|
Terry | 1|project_a|coordination|
Terry | 2|project_c|staff       |

select tvp.person,
tvp.project as project_1, tvp.role as role_1 ,
tvp2.project as project_2, tvp2.role as role_2,
tvp3.project as project_3, tvp3.role as role_3,
tvp4.project as project_4, tvp4.role as role_4
from tmp_view_projects_unnest tvp
left join tmp_view_projects_unnest tvp2 on tvp2.person = tvp.person and tvp2.rn =2
left join tmp_view_projects_unnest tvp3 on tvp3.person = tvp.person and tvp3.rn =3
left join tmp_view_projects_unnest tvp4 on tvp4.person = tvp.person and tvp4.rn =4
where tvp.rn=1;

person|project_1|role_1      |project_2|role_2      |project_3|role_3      |project_4|role_4|
------|---------|------------|---------|------------|---------|------------|---------|------|
Alice |project_a|leader      |project_d|coordination|project_c|support     |         |      |
Bob   |project_d|coordination|project_c|coordination|project_c|support     |         |      |
Maggie|project_c|leader      |         |            |         |            |         |      |
Mandy |project_d|leader      |         |            |         |            |         |      |
Marry |project_d|staff       |         |            |         |            |         |      |
Peter |project_d|support     |project_c|staff       |project_a|coordination|         |      |
Terry |project_a|coordination|project_c|staff       |         |            |         |      |

Collectives™ on Stack Overflow

how to unnest multiple arrays and transpose table in postgres 9.5

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related