[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 976
  • Last Modified:

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?



  • 2
2 Solutions
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
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now