SQL transpose and concatenate

Question

I have a table having data like:

a 1
a 2
a 3
a 4
b 1
b 2
b 3

CREATE TABLE piv1([name] varchar(5), [no] int);

INSERT INTO piv1
    ([name], [no])
VALUES
    ('a', 1),
    ('a', 2),
    ('a', 3),
    ('a', 4),
    ('b', 1),
    ('b', 2),
    ('b', 3);

SELECT *
FROM piv1 
            PIVOT (
                   MAX(no)
                   FOR no
                   IN ([1],[2],[3],[4])
                   )p

But I'm getting output like:

1 2 3 4    name
1 2 3 4    a
1 2 3 null b  

My desired output:

a 1,2,3,4
b 1,2,3

Answer 1

You would seem to want string_agg():

select name, string_agg(no, ',') within group (order by no)
from piv1
group by name;