How to collate multiple rows into single row


I have a table with multiple rows that I need to collate into a single row.

eg (not real data)

ID, Name, Car, Colour

1, James, Ford, Blue
2, Peter, Volvo, Green
3, James, GM, Red,
4, Peter, Ford, Blue
5, James, Volvo, Blue
6, Peter, GM, Green

I would like this returned as :-

James, Ford, Blue, GM, Red, Volvo, Blue
Peter,  Volvo, Green, Ford, Blue, GM, Green

Any thoughts?



Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
create function dbo.ConcatList ( @Name varchar(100) )
returns varchar(8000)
      declare @res varchar(8000)
      select @res = coalesce(@res + ',', '') + car+','+Color
      from yourtable
      where Name = @Name
      return (@res)

and use it like this:

select Name, dbo.ConcatList(Name) as list
from yourtable
group by Name

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JAMESAuthor Commented:
Sorry I didnt meant to have a sinlge column with the data separated by commas - i just meant that as each piece of data between commas to appear in a new col.

JAMESAuthor Commented:
I should also add I can't add any functions or tables to this database as it's not ours and we are merely extracting info from it.

Thanks again.
Ken SelviaRetiredCommented:
This is probably not the optimal way to do this, but it should work.  If you have 10,000 rows or more it probably needs to be done differently but I'm not sure there is a real good way to do it if you can't create any tables, views, functions or procedures on the db.

I also wonder how you are going to display the results.  There could be any number of columns returned.  

How are you going to run this if you can't create a procedure?

      @sql varchar(8000),
      @lastname varchar(30),
      @list varchar(8000),
      @columns int

create table #t (ID int, Name varchar(30), Car varchar(30), Colour varchar(30) )
insert #t select 1, 'James', 'Ford', 'Blue'
insert #t select 2, 'Peter', 'Volvo', 'Green'
insert #t select 3, 'James', 'GM', 'Red'
insert #t select 4, 'Peter', 'Ford', 'Blue'
insert #t select 5, 'James', 'Volvo', 'Blue'
insert #t select 6, 'Peter', 'GM', 'Green'

select Name, Car+','+Colour cc, space(8000) as cclist
into #x
from #t
order by Name

select @list = '', @lastname ='', @sql=''

update #x
set @list = cclist = case when @lastname <> Name then cc else @list + ',' + cc end,
    @lastname = Name

select @columns = max ( len(cclist) - len(replace(cclist,',',''))) + 1 from #x

create table #zz (Name varchar(30))

select @sql = @sql + r.cmd from
      select 'alter table #zz add col'+convert(varchar,Number) + ' varchar(30) NULL ' cmd
      from master..spt_values s
      where s.Number between 1 and @columns and s.type = 'P'
) r
exec (@sql)

select top 1 @lastname = Name, @list = max(cclist) from #x group by Name

while @@rowcount > 0
      set @sql = 'insert #zz select '''+@lastname+''', ''' + replace (@list, ',', ''',''') + ''''
      + replicate ( ' ,Null ', @columns -  ( len(@list) - len(replace(@list,',','') ) + 1 ) )
       exec (@sql)

      select top 1 @lastname = Name, @list = max(cclist) from #x
      group by Name
      having Name > @lastname

select * from #zz

drop table #t
drop table #x
drop table #zz
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.