We help IT Professionals succeed at work.

How to collate multiple rows into single row

JAMES
JAMES asked
on
Hi,

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?

Thanks.

James.



Comment
Watch Question

Database Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
create function dbo.ConcatList ( @Name varchar(100) )
returns varchar(8000)
as
begin
      declare @res varchar(8000)
      select @res = coalesce(@res + ',', '') + car+','+Color
      from yourtable
      where Name = @Name
      return (@res)
end


and use it like this:

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

Author

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.

Thanks.

Author

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 SelviaConsultant
Commented:
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?


declare
      @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
begin
      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
end

select * from #zz

drop table #t
drop table #x
drop table #zz

Explore More ContentExplore courses, solutions, and other research materials related to this topic.