sqlpieclouddb

How to get pivot with no specific pattern


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!)


Solution

  • 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;
    

    enter image description here