June 24, 2005 at 8:01 am
Hello Experts!!!
I need to colapse several rows into a single row and concatinate a text field across the rows. I can't see how to do it. Anybody care to make a stab?
Eg.
input
Sales_Center Product
1 A
1 B
1 C
2 A
2 C
desired output
Sales_Center Products_Sold
1 A, B, C
2 A, C
I'd like to do it as a column UDF (which SQL Server doesn't have). such that this would be the sql
select sales_center, dbo.udf_concat(product,', '{delimiter}) as Products_Sold
from ...
group by sales_center
I found an example that uses calc fields, but don't know how to rework since calculated columns can be combined with std selects.
Thanks in advance
Doug Little
/*************************************************************************************************
Purpose:To concatenate the values of a column in all rows into one row.
Written by:Allan Mitchel
http://www.allisonmitchell.com
Tested on: SQL Server 7.0 and SQL Server 2000
Date modified:March-22-2001 6:30 PM
Email: vyaskn@hotmail.com
NOTE:A limitation to be aware of is that varchar can go upto a max size of
8000 characters. If your data occupies more space, the output will be
truncated.
*************************************************************************************************/
USE pubs
GO
DECLARE @title_ids varchar(150), @delimiter char
SET @delimiter = ','
SELECT @title_ids=COALESCE(@title_ids + @delimiter, '') + title_id, title_id FROM titl
SELECT @title_ids AS [List of Title IDs]
June 24, 2005 at 9:12 am
I think you already answer your own question:
create function dbo.udf_concat_products (@Center int )
returs varchar(400)
as
begin
declare @str varchar(400), @delimiter char(1)
SET @delimiter = ','
SELECT @str =COALESCE(@str + @delimiter, '') + Product
FROM Products
where Sales_Center = @center
return @str
end
select sales_center, dbo.udf_concat_products (sales_center) as Products_Sold
from Sales
group by sales_center
* Noel
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply