SQL PIVOT or another way?

1

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??

2

PIVOT is the easiest

3

I prefer XML.

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

4

how do you code it?

5

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

insert into @Test values
(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')

--Original Table
select * from @Test

--What you want
select Id,Firstname,Surname
from (
select distinct
a.Id,
a.Firstname,
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