I am looking for a way to handle following pivot situation.
Example data:
| name |
|---|
| A |
| Alan |
| B |
| Bacon |
| Bob |
| Boyd |
| C |
| Carl |
| Chris |
| Colden |
| D |
| Diana |
Now I want to get this output:
| column1 | column2 |
|---|---|
| A | Alan |
| B | Bacon, Bob, Boyd |
| C | Carl, Chris, Colden |
| D | Diana |
I tried:
SELECT SUBSTRING(name, 1, 1) AS column1,
STRING_AGG(name, ', ' ORDER BY name) AS column2
FROM info
GROUP BY SUBSTRING(name, 1, 1)
ORDER BY column1;
but I got:
| column1 | column2 |
|---|---|
| A | A, Alan |
| B | B, Bacon, Bob, Boyd |
| C | C, Carl, Chris, Colden |
| D | D, Diana |
I don’t know how to remove ‘A’, ‘B’, ‘C’, ‘D’ in column2, is there any way?(BTW, I use PieCloudDB database, if you haven’t heard of it, you can use PostgreSQL to demo instead. Thank You!)
1
Sorry, my reputation is not high enough for me to add a comment, I think you can simply use ‘case when’ to filter the ‘A’,’B’…
SELECT SUBSTRING(name, 1, 1) AS column1,
STRING_AGG(CASE WHEN LENGTH(name) > 1 THEN name END, ', ' ORDER BY name) AS column2
FROM info
GROUP BY SUBSTRING(name, 1, 1)
ORDER BY column1;
If column 2 don’t have value, do you want to show record? if you don’t want, you can try my way:
SELECT
SUBSTRING(name, 1, 1) AS column1,
STRING_AGG(name, ', ' ORDER BY name) AS column2
FROM
info
WHERE
LENGTH(name) > 1 -- Exclude single-letter rows (like 'A', 'B', etc.)
GROUP BY
SUBSTRING(name, 1, 1)
ORDER BY
column1;