February 10, 2006 at 10:28 am
Hi, is there a SQL function that can concatenate values into one column, like this:
I have multiple rows in table:
-----------------------------------
OrderID ItemID
1 10
1 11
2 12
2 13
.........
------------------------------------
I want to get a result like this:
OrderID ItemIDs
1 10, 11
2 12, 13
.........
Can this be done qith a simple query? Thanks in advance !
February 10, 2006 at 10:54 am
No, Not a simple query.
In sql 2000, you can use a function.
Works well,
-- =============================================
-- Create scalar function (FN)
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'fn_ConcatStrings')
DROP FUNCTION fn_ConcatStrings
GO
CREATE FUNCTION fn_ConcatStrings
(@OrderID int)
RETURNS varchar(500)
AS
BEGIN
declare @String varchar(500)
select @String = coalesce(@String,'') + ltrim(str(ItemID)) + ','
from mytable
where [OrderID] = @ID
if Right(@String,1) = ','
set @String = substring(@String,1,len(@String) -1)
return @String
END
GO
-- =============================================
-- Example to execute function
-- =============================================
Select OrderID, dbo.fn_ConcatStrings (OrderID)
From Mytable
February 10, 2006 at 11:15 am
Ray..gives you a good solution...
but you may want to call it...
Select distinct OrderID, dbo.fn_ConcatStrings (OrderID)
From Mytable
Mathew J Kulangara
sqladventures.blogspot.com
February 10, 2006 at 11:40 am
My question would be "Why do you want to do this"?
If it is something that the front end application requires then I would return the original recordset but have the app do a String.Join() to concatenate the stuff.
String manipulation in SQL is S....L....O.....W.
I have had to do something similar to what you described but rather than do it on the fly I kept a specific table with 2 fields, OrderID and ItemString.
In my case this was populated by a specific proc after the order was complete.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply