Return 1 column of many child rows as string

  • Scenario: Parent Table and Child table

    Needs: for each parent row, make a string consisting of one of the child rows columns so that only one resulting parent row is returned with the string as the last column:

    ie think of customers as parent and orders as child

    The columns in the return set should be something like:

    cust_number,cust_name,cust_phone,cust_order_numbers

    so some sample data for a customer with 6 orders would look like:

    "H012", "Amber Customer","9025551234","1247 1248 1262 1264 1270 1398"

    how can I create a select to do this without using a stored proc - just pure SQL?

    Thanks,

    caper


    CaperSailor

    Outside of a dog, a book is a man's best friend. Inside of a dog, it's too dark to read." Groucho Marx, 1890-1977

  • a UDF would be the best way to go

    CREATE FUNCTION dbo.UF_ReturnOrders (@Cust_num int)

    RETURNS varchar(255)

    AS

    BEGIN

    DECLARE @RetStr varchar(255)

    SELECT @RetStr = ISNULL(@RetStr + ' ' + CONVERT(varchar(20),Order_numbers),CONVERT(varchar(20),Order_numbers))

    FROM Orders

    WHERE (Cust_num = @Cust_num)

    RETURN @RetStr

    END

    SELECT *,dbo.UF_ReturnOrders(cust_number) as cust_numbers

    FROM customers

  • Yes, that does seem the easiest - I was just thinking that I wasn't forgeting something in SQL that would allow me to do it completely in-line...something along the lines of CUBE - but again, I wasn't thinking clearly on the matter...

    Thanks so much!


    CaperSailor

    Outside of a dog, a book is a man's best friend. Inside of a dog, it's too dark to read." Groucho Marx, 1890-1977

  • actually - i have come across the same problem many times myself - and have always opted for this solution . it would be interesting to see if this can be done in one single sql statement - without using UDFs - any takers??

  • Why not solving it with a nested select instead of the UDF? Preformance shouldn't be so much different though...

    Hans

  • But you cannot (at least I have not seen it done) create a single nested SQL statement to return a single string representing multiple rows. The UDF apporach is all I am aware of.

  • true - the only SQL only options i can think of are restrictive and would not be true solutions

    Hans - can you show us what kind of nested select you have got

  • Sorry guys :/ I was too hasty in responding, I had not tested my solution before proposing it. And you are right, it seems quite difficult trying to solve it with a nested query.

    Meanwhile I will keep trying but I guess I will not succeed.

    Hans

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply