«
»

Here’s a neat trick when you need to concatenate child rows in a SQL Select statement. What do I mean by that? Its a little hard to explain, so I’ll try to illustrate below.

Say you have these two database tables:

orders
OrderID OrderNotes
1 Notes about order 1
2 Notes about order 2
3 Notes about order 3
orderLines
LineItemID OrderID LineNotes
50 1 ABC
51 1 XYZ
52 2 HJK
53 2 DEF
54 2 KLM

Using the SQL trick below you will end up with a result set that looks like this:

OrderID OrderNotes listOfLineNotes
1 Notes about order 1 ABC,XYZ
2 Notes about order 2 HJK,DEF,KLM
3 Notes about order 3 NULL

When would you need to do this? I’ve need it a couple times. One was when we were grabbing database records to index into Verity. We wanted not only data from the orders table, but also wanted to index all the notes on each line item, which were separate records in a different table. We didn’t want to index these separately, because if there was a match in a Verity search we wanted to return the order, not the line item. Using the query below we were able to combine all this data into one query.

SELECT *
FROM orders AS o
CROSS APPLY (
	SELECT linenotes + ','
	FROM orderLines AS ol
	WHERE ol.orderID = o.orderID 
	FOR XML PATH('') )
temp ( listOfLineNotes )

Using this code, listOfLineNotes will be a comma separated list of all the line item notes for this order.

Another time this trick came in handy was when we needed to get some records from a literature database table to display on our public website. We were going to do some filtering using JavaScript, so needed a field in each literature item row that contained a list of all the categories the item was in. This was an easy way to accomplish that.

This entry was posted on 20 October 2010 at 10:39 am and is filed under SQL. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

8 Comments

  1. damiththa says:

    Thank you Ryan this is great.
    I always wondered how this can be accomplished but now I know how.

    20 October 2010, 12:50 pm

  2. Julian Halliwell says:

    In MySQL you can accomplish the same in a slightly more transparent way with GROUP_CONCAT. As you say great for any time you need to de-normalise such as populating a search index.

    SELECT
    o.orderID
    ,o.orderNotes
    ,GROUP_CONCAT( lineNotes ) AS listOfLineNotes
    FROM
    orders o
    LEFT JOIN
    orderLines ol ON ol.orderID = o.orderID
    GROUP BY
    o.orderID

    23 October 2010, 1:50 am

  3. Ryan says:

    Good stuff Julian, thanks.

    23 October 2010, 8:13 am

  4. Bill Richard says:

    Interesting, I had no idea you could use CROSS APPLY like that. Thanks

    5 November 2010, 2:05 pm

  5. Bryan says:

    Thanks for posting this.  It was exactly what I needed!

    One minor question though: Is there a way within the query itself to strip the comma off of the last item?  For example, when I run the query you posted I get the value "ABC,XYZ," instead of "ABC,XYZ" for the listOfLineNotes field in the first row.

    30 December 2010, 2:04 pm

  6. Ryan says:

    You might be able to use the Left() function to remove the trailing comma character.  I don't have my database up now so I can't test it, but something like Left( [computed value], Len([computed value])-1)

    1 January 2011, 10:39 pm

  7. Oscar says:

    to get rid of the comma you can use STUFF… in Ryan’s example you would basically declare your columns and ‘STUFF’ the one with the results of the CROSS APPLY like this:

    SELECT OrderID, OrderNotes, STUFF(listOfLineNotes,1,1,”) as listOfLineNotes
    FROM orders AS o
    CROSS APPLY (
    SELECT linenotes + ‘,’
    FROM orderLines AS ol
    WHERE ol.orderID = o.orderID
    FOR XML PATH(”) )
    temp ( listOfLineNotes )

    10 April 2013, 8:21 am

  8. Ryan says:

    Great tip Oscar, thanks!

    10 April 2013, 8:32 am