SQL PIVOT or another way?

I don't know the jargon that well.

I can do basic queries.

say you have a list as rows table1

1 Julie dave
1 Julie bob
1 Julie dick
2 Sandy greg
2 Sandy Cindy
3 Tracy mike
3 Tracy rich
3 Tracy lucy
3 Tracy sara

you want your end result to be this: table2

1 Julie dave bob dick
2 Sandy greg cindy
3. Tracy mike rich lucy sara

What would the best way to do this??

PIVOT is the easiest

I prefer XML.

But what should be the results for:
1 Julie dave
1 bob Julie
1 Jim dave

how do you code it?

declare @Test table (Id int,Firstname varchar(255),Surname varchar(255))

insert into @Test values

--Original Table
select * from @Test

--What you want
select Id,Firstname,Surname
from (
select distinct
stuff(t.Surname,1,1,'') as Surname
from @Test a
cross apply (
select ', '+Surname from @Test b
where a.id = b.id and a.Firstname = b.Firstname
for xml path (''))T (Surname)
) main

1 Like