What's wrong with this recursive CTE and more importantly, what general concept am I failing to comprehend?

Question

OK, I'm looking at the problem of turning a set of records into a delimited list. It's an old problem, there are several approaches, and in many cases, I shouldn't even be doing this in the DB. But, in this case, I want to, and I want to use a recursive CTE.

And as my title might suggest, I'm frustrated by the difficulty that I'm having really grasping the concept. In the past, I've muddled through this problem with code snippets from books or internet posts and adapted them and gotten them to work. But it's always hard and I'm not really mastering the technique. I looked for a very basic implementation of the technique and landed at this answer that I'm using as a model: https://stackoverflow.com/a/9726839/13748

So right now, I have this temp table #POSO with the following contents:

id  inspectionLogKey PO            SO
--- ---------------- ------------- ---------
1   7                374534-6988   SO37047
2   7                374534-5464   SO34110
3   7                374534-7135   SO37377
4   7                374534-5284   SO33863
5   7                374534-6710   SO36506
6   7                374534-5084   SO33565

And from these data, I'd like to produce a comma-delimited list of the PO column's values. I'm trying to do that with this (and a bunch of frustrating permutations of this):

WITH POlists(id,POs) AS
(
    SELECT p1.id, CONVERT(VARCHAR(MAX),p1.PO) as POs --anchor
    FROM #POSO p1
    WHERE p1.id = 1

    UNION ALL

    SELECT p2.id, POs + ',' + p2.PO --recursive
    FROM #POSO p2
        join POlists ON p2.id + 1 = POlists.id
)
SELECT * FROM POlists;

Which returns this:

id  POs
--- -----------
1   374534-6988

I know that one of you is going to be able to point out the code error in the next eight minutes. But I'm hoping that someone can reframe what this is doing in such a way that I can actually grok it and take it away from this encounter as a tool for future use.

And maybe once that happens, I'll know enough to answer this on my own, but can this query handle both the PO and SO columns at the same time, producing two fields, each a comma-delimited list of the the two respective columns?

Thanks for your time!

You said you are trying to create a comma delimited list of values. You can do this a lot easier than with a recursive cte. You can use FOR XML to this a lot easier, and faster. http://www.sqlservercentral.com/articles/comma+separated+list/71700/ — Sean Lange, Sep 04 '15 at 20:17
@SeanLange - The OP appears aware of differing methods but states "in this case, I want to, and I want to use a recursive CTE." — Martin Smith, Sep 04 '15 at 20:22
But a recursive cte is an inefficient tool for the job. Just because it can be done like that doesn't mean it should. — Sean Lange, Sep 04 '15 at 20:40
That's right, I've used FOR XML PATH for this purpose in the past as well. @SeanLange when you say "faster" do you mean faster to write the code or faster-executing code? — clweeks, Sep 04 '15 at 20:40
Definitely faster to write and in my experience faster to execute as well. Depending on the depth of course. :) — Sean Lange, Sep 04 '15 at 20:41

Martin Smith · Accepted Answer · 2015-09-04T20:22:55.553

1

p2.id + 1 = POlists.id should be p2.id - 1 = POlists.id the only error is algebra.

Your first id is 1. There is no row such that p2.id + 1 =1 as you have no row with an id of 0 so it stops there.

Though p2.id = POlists.id + 1 would be preferable to potentially allow an index seek in the recursive part.

And you probably want a level column in there to just return the completed string.

WITH POlists(id,POs, Lvl) AS
(
    SELECT p1.id, CONVERT(VARCHAR(MAX),p1.PO) as POs, 0 AS Lvl
    FROM #POSO p1
    WHERE p1.id = 1

    UNION ALL

    SELECT p2.id, POs + ',' + p2.PO, Lvl + 1
    FROM #POSO p2
        join POlists ON p2.id = POlists.id + 1
)
SELECT TOP 1 * 
FROM POlists
ORDER BY Lvl DESC;

edited Sep 04 '15 at 20:22

answered Sep 04 '15 at 20:17

Martin Smith

438,706
87
741
845

Wow! I was just blind to that. So, regarding the level, why can't I just take the result associated with the highest ID? LIke `SELECT TOP 1 POs FROM POlists ORDER BY id DESC` I mean, it works right now, but is there some other case I'm not envisioning where it won't? I'm thinking not because I create the ID in that temp table right before executing this query. – clweeks Sep 04 '15 at 20:36
1

@clweeks - Good point - the rest of the code relies on `id` being sequential anyway so you might as well use that. Technically an identity column doesn't guarantee sequential but for a temp table if you are inserting in a single statement I've never seen gaps. – Martin Smith Sep 04 '15 at 20:45

What's wrong with this recursive CTE and more importantly, what general concept am I failing to comprehend?

1 Answers1