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

Question

So I've got this table

Email                     Username
------------------------- -------------------------
[email protected]                   a1                       
[email protected]                   a2                       
[email protected]                   a3                       
[email protected]                   a4                       
[email protected]                   b1                       
[email protected]                   b2                       
[email protected]                   b3                       
[email protected]                   b4                       
[email protected]                   c1                       
[email protected]                   c2                       
[email protected]                   c3                       
[email protected]                   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
------------------------- -------------------------
[email protected]                   a1, a2, a3, a4
[email protected]                   b1, b2, b3, b4 
[email protected]                   c1, c2, c3, c4 
[email protected]                   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 ('[email protected]','a1'), ('[email protected]','a2'), ('[email protected]','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