Merge column data in alphabetical order

[bmacbmac]

Hi, I have a table:

create table #temp (col1 varchar(25), col2 varchar(25), col3 varchar(25), col4 varchar(25), col5 varchar(200))
go
insert into #temp (col1, col2, col3) values ('SMITH', 'JOHNSON', 'ANDERSON')
insert into #temp (col1, col2, col3) values ('JOHNSON BILL', 'JOHNSON ALAN', 'JOHNSON JACOB')
insert into #temp (col1, col2, col3) values ('KIMBER', 'ANTON', 'BUNDY')
insert into #temp (col1, col2, col3) values ('COOLER', 'KYLER', 'COMERN')

I would like to merge Col1, Col2, Col3 into Col4 in alphabetical order separated with a comma to end up with:

Col4:
ANDERSON, JOHNSON, SMITH
JOHNSON ALAN, JOHNSON BILL, JOHNSON JACOB
ANTON, BUNDY, KIMBER
COMERN, COOLER, KYLER


Can anyone think of how I can do this?

Thanks!

Brian

 

[TamarGranor]

I'm pretty sure you can do something with FOR XML PATH(''), but I tried a few variations and couldn't get the right answer.

Tamar

 

[Olaf Doschke]

In your test data the situation is simple, but in the real world table, are there additional fields, which need to be sorted along the names? Overall you better don't sort data within records, but put them in a separate table. Your design is not normalized and this leads to such totally uncommon problems.

Bye, Olaf.

 

[SkipVought]

Ditto to normalization!

 

[gk53]

try that

create table #temp (col1 varchar(25), col2 varchar(25), col3 varchar(25), col4 varchar(25), col5 varchar(200))
go
insert into #temp (col1, col2, col3) values ('SMITH', 'JOHNSON', 'ANDERSON')
insert into #temp (col1, col2, col3) values ('JOHNSON BILL', 'JOHNSON ALAN', 'JOHNSON JACOB')
insert into #temp (col1, col2, col3) values ('KIMBER', 'ANTON', 'BUNDY')
insert into #temp (col1, col2, col3) values ('COOLER', 'KYLER', 'COMERN') 

;with t as 
(
select col1,
		col2,
		col3,
		col4 = col1 + ', ' + col2 + ', ' + col3
from #temp
)
select *
from t
order by col4

 

[TamarGranor]

George - If I understand the original question, he wants the data from the first three columns sorted before appending it into col4.

Tamar

 

[philhege]

Uglier than sin, but it works in this little sample world:

declare @namelist varchar(128)
set @namelist = ''
declare @row_count int
declare @currentrow int
set @currentrow = 1
create table #tempout (col1 varchar(25), col2 varchar(25), col3 varchar(25), col4 varchar(25), col5 varchar(200))

create table #temp (rownumber int identity(1,1), col1 varchar(25), col2 varchar(25), col3 varchar(25), col4 varchar(25), col5 varchar(200))


insert into #temp (col1, col2, col3) values ('SMITH', 'JOHNSON', 'ANDERSON')
insert into #temp (col1, col2, col3) values ('JOHNSON BILL', 'JOHNSON ALAN', 'JOHNSON JACOB')
insert into #temp (col1, col2, col3) values ('KIMBER', 'ANTON', 'BUNDY')
insert into #temp (col1, col2, col3) values ('COOLER', 'KYLER', 'COMERN') ;
select * into #turntable
from
(
select col1 as namesort, rownumber FROM #temp
UNION
select col2, rownumber from #temp
union
select col3, rownumber FROM #temp
) as a
select @row_count = COUNT(*) from #temp
WHILE @currentrow <= @row_count
BEGIN
select @namelist = @namelist + coalesce(namesort+', ','') from #turntable
where rownumber = @currentrow
insert into #tempout
(col1,col2,col3,col5)
select col1, col2, col3, substring(@namelist,1,len(@namelist)-1) from #temp
where rownumber = @currentrow
set @namelist = ''
set @currentrow = @currentrow + 1
END
select * from #tempout
/*
select coalesce(col1 +', ','') as blah
group by number
from #temp
*/
drop table #temp
drop table #turntable
drop table #tempout

-----------
With business clients like mine, you'd be better off herding cats.