Create sets within groups using SQL in PostgreSQL

Question

I am trying to create sets from existing rows but having difficulty. I have some success but it is not coming to what I am expecting.

Here is my existing data and my desired outcome. I tried many grouping and windows functions but not able to come up with a solution.

**Existing Data**
User    Date        Flags
John    2024-01-11  A,B,C
Doe     2024-01-15  A,B,C
John    2024-01-18  A,B,C,A,B,C
Doe     2024-01-25  A,B,C,A,B,C,A,B,C

**Desired Result**
User    Date        Flags
John    2024-01-11  A,B,C
Doe     2024-01-15  A,B,C
John    2024-01-18  A,B,C
John    2024-01-18  A,B,C
Doe     2024-01-25  A,B,C
Doe     2024-01-25  A,B,C
Doe     2024-01-25  A,B,C

klin · Accepted Answer · 2024-08-03 12:23:55Z

3

You need to unnest the flags and get their ordinal numbers within groups by (user, date, flag), then aggregate the data using these numbers.

select "user", "date", string_agg(flag, ',' order by flag) as flags
from (
    select "user", "date", flag, row_number() over w
    from my_table
    cross join string_to_table("flags", ',') as f(flag)
    window w as (partition by "user", "date", flag)
    ) s
group by "user", "date", row_number
order by "date", "user"

Test it in db<>fiddle.

edited Aug 3, 2024 at 12:23

answered Aug 3, 2024 at 0:21

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

Create sets within groups using SQL in PostgreSQL

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