Sep/10
6
Comma Delimited List in TSQL
No comments · Posted by Larry.Smithmier in Examples, Snippit, SQL, Uncategorized
I am often tasked with making modifications to existing UIs, and tend to try and limit the impact I have by pushing a lot of the formatting and BL into the database. One piece of code that I regularly have to look up is the code to convert a result set into a comma delimited list. Here is the SQL 2005/2008 code that I just created for use in a client environment:
CREATE FUNCTION [dbo].[fn_LinkedString]
(
-- Add the parameters for the function here
@ID varchar(10)
)
RETURNS nvarchar(1000)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result nvarchar(1000)
-- Add the T-SQL statements to compute the return value here
SELECT @Result= x.List
FROM
(
SELECT
L1.ID,
List = SUBSTRING((SELECT (', '+ LinkedID)
FROM Linked L2
WHERE
L1.ID = L2.ID
ORDER BY
ID,
LinkedID
FOR XML PATH('')
), 3, 1000) FROM Linked L1
GROUP BY ID
) x
WHERE
x.ID = @ID
-- Return the result of the function
RETURN @Result
END
I wrapped the code in a scalar valued function to make it easier to use and to keep the code clean when used.
