SQL Server - trying to format a table, maybe with PIVOT

Question

So I've got this table

Email                     Username
------------------------- -------------------------
a@a.com                   a1                       
a@a.com                   a2                       
a@a.com                   a3                       
a@a.com                   a4                       
b@b.com                   b1                       
b@b.com                   b2                       
b@b.com                   b3                       
b@b.com                   b4                       
c@c.com                   c1                       
c@c.com                   c2                       
c@c.com                   c3                       
c@c.com                   c4                       

(12 row(s) affected)

But I want each e-mail address to appear once, then all associated Usernames to be listed after it, if that's possible..

Any help whatsoever is appreciated, Cheers.

Edit

Ok, so apparently, all the usernames will come under one column, all concatenated together, sounds kinda dumb, but that's what I've been asked for.

The output I want would be

Email                     Username
------------------------- -------------------------
a@a.com                   a1, a2, a3, a4
b@b.com                   b1, b2, b3, b4 
c@c.com                   c1, c2, c3, c4 
d@d.com                   d1, d2, d3, d4

You're looking something like this? http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string/545672#545672 — James Z, Jul 08 '15 at 13:55

score 1 · Accepted Answer · answered Jul 08 '15 at 14:05

Try this using FOR XML PATH

select
    Email,
    Username = 
        stuff((
            select
                ', ' + t2.Username
            from #table1 t2
            where
                t2.Email = t1.Email
            group by t2.Username
            for xml path(''), type).value('.', 'varchar(max)'
        ), 1, 2, '')
from #table1 t1

score 0 · Answer 2 · answered Jul 08 '15 at 14:04

declare @t table (Id Varchar(10),username varchar(10))
insert into @t(Id,username)values ('a@a.com','a1'), ('a@a.com','a2'), ('a@a.com','a3'),


select DISTINCT Id,substring(
        (
            Select ','+t.username  AS [text()]
            From @t t
            Where t.Id = t.Id
            ORDER BY tt.Id
            For XML PATH ('')
        ), 2, 1000)Username  from @t tt

SQL Server - trying to format a table, maybe with PIVOT

2 Answers2