How do I gather information from multiple records into one record?

[lilrat]

I have a table that contains customers address information along with orderid's. Each new orderid represents a new row in the table
I would like to gather all of the orderid's for each customer, seperated by a delimiter, into one column. This would result in one record per customer instead of many.
I realize that I will need to create another table. I am just having trouble with the SQL.

I am using Sever2000. Thanks

 

[jrbarnett]

Sybase SQL Anywhere has a List() function that will do what you want generating a comma separated list, but this is not an ANSI standard feature. I am not sure if SQL Server 2000 supports this, but it may be because they shared an earlier codebase.

John

 

[swampBoogie]

List is not supported in SQL server 2000.

The easiest way would be to create a function that concatenates the orderids for a customer.

create function owner.sb_4(@customerid int)
returns varchar(200)
as
begin
declare @list varchar(200)

select coalesce(@list + ',','') + cast(orderid as varchar(10))
from orderInfo 
where customerid = @customerid
return @list
end

and use that function in your select