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

Related Posts:

• T-SQL: Generate Sequences in a Loop Query
• T-SQL: Optional Parameters in Select Query (Using ISNULL)
• MSSQL Tip: Get Primary Table in Relationship
• MSSQL Tip: Get Table Primary Key Column(s)
• ROWNUM [ PL/SQL ] Analog for T-SQL
• MSSQL Tip: Get Connected Columns in Related Tables
• How To Shrink Transaction Log in MSSQL 2000
• T-SQL: Output Parameter in sp_executesql
• MSSQL: Change tables owner to dbo with sp_changeobjectowner
• MSSQL: Recompile Stored Procedures, Views, Functions