Consider ‘Table 1’ below:
| id | x |
|---|---|
| 1 | A |
| 1 | B |
| 1 | C |
| 2 | B |
| 2 | C |
| 3 | A |
I want to query Table 1 to produce the following:
| id | x | grps |
|---|---|---|
| 1 | A | B, C |
| 1 | B | A, C |
| 1 | C | A, B |
| 2 | B | C |
| 2 | C | B |
| 3 | A | A |
Using string_agg and grouping by id produces “A, B, C” for all id 1 rows, and I want to exclude the x value from grps for that row (where x = “A”, grps should equal “B, C”, etc.) Is there some self-join/string_agg/grouping trick?