Wednesday, March 12, 2008

HowTo Convert Table Values to Comma-Delimited String

We have 2 tables related as one-to-many. How can we fetch parent table field, and child table values comma-delimited ? The function below demonstrates a technique how to convert table values into comma-delimited string with one query.


ALTER FUNCTION F_GET_STR
(@p_order_id int)
RETURNS varchar(1000)
AS
BEGIN

DECLARE @p_str VARCHAR(1000)
SET @p_str = ''

SELECT @p_str = @p_str + ',' + CAST(productid AS VARCHAR(6))
FROM [order details]
WHERE orderid = @p_order_id

RETURN @p_str




END

Use this way:


SELECT orderid ,dbo.F_GET_STR(orderid) FROM orders

No comments: