How to concat all values associated with a key?-CodePudding

I have the following schema:

CREATE TABLE table1 
(
    user,
    phoneType, --from ['A','B','C','D', 'E'], user can have any number of any type
    uniquePhoneID, --unique string identifying the phone
    day_id --date; record does not necessarily exist for every seen user   phoneType every day, represented as number in example
);

INSERT INTO table1 
VALUES (1, 'A', xyz, 1),
       (1, 'A', abc, 1),
       (1, 'B', def, 2),
       (1, 'A', xyz, 2),
       (1, 'C', hij, 4),
       (1, 'A' xyz, 5),
       (2, 'C', w, 9),
       (2, 'D', z, 10),
       (2, 'A', p, 10),
       (2, 'E', c, 11),
       (3, 'A', r, 19),
       (3, 'B', q, 19),
       (3, 'B', q, 20),
       (3, 'B', f, 20),
       (3, 'B', y, 21);

A single user, uniquePhoneID, day_id will only show up at most once, but not necessarily at all on any given day. I am looking to concatenate each user in the table with their 4 phoneTypes in alphabetical order, so the result is as follows:

1 | AABC
2 | ACDE
3 | ABBB

I have tried a few different ways of doing this but I am unsure how to get the answer I am looking for.

CodePudding user response：

I think user is a reserved word, so you will have to resolve that. Otherwise, I think something like this will work for you:

select user, string_agg (phonetype, '' order by phonetype)
from table1
group by user

-- EDIT 4/21/2022 --

Aah, okay. I did not glean that from the original question.

What if you used the distinct on the original table before the aggregation?

select userid, string_agg (phonetype, '' order by phonetype)
from (select distinct userid, phonetype, uniquephoneid from table1) x
group by userid

I got these results from this version:

1   AABC
2   ACDE
3   ABBB

If that logic still doesn't work, can you alter the sample data to find an example where it fails?