HowTo Convert Table Values to CommaDelimited 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