SQLTeam.com | Weblogs | Forums

Need help with simple query

#1

ok, this is a real simple one, i could probably do it if i knew what to search for :frowning:

i have a table with 2 columns, Email and Username

The data looks like this

EMAIL, USERNAME
email1, user1
email1, user2
email2, user1
email3, user1
email3, user2

I want to do a select with a subquery? to get the following format.

EMAIL, USERNAME
email1, user1 user2
email2, user1
email3, user1 user2

Any pointers or help very much appreciated, i'm definitely rusty in sql :frowning:

#2 
select 
	a.email,
	stuff(b.usernames,1,1,'') 
from
	(select distinct email from Tbl) a
	cross apply
	(
		select ',' + b.username
		from Tbl b
		where b.email = a.email
		order by b.username 
		for xml path('')
	)b (usernames);