CTE with Recursion Example

Common Table Expressions with Recursion using WITH. A Common Table Expression is a temporary result set upon which we can work against. It's kind of like a sub query in memory that we can manipulate. Referencing this temporary table to create a subset is an example of recursion. This example may help explain and provide reference to create your own CTE with Recursion query.

This is very easy in ColdFusion but not so in Microsoft SQL Server or TSQL. We'd like to keep it on the database side because it is so much faster with less overhead on servers and network. It is a lot faster than trying to use sub-queries too.

We are given this table called dbo.JoeTest1:

view plain print about
1EVID    BillDocket
2191    External:277
3192    External:277
4193    External:277
5194    External:277
6195    Internal:117
7197    Internal:HB2255
8206    External:4;5;7
9206    Action:123
10206    Combo:1;0;67
11206    Action:1321;5445;3545
12206    Internal:1233;1323
13207    Internal:11;22;33
14207    External:44;55;66
15211    External:729
16214    External:481

Notice EVIDs 206 and 207. They have multiple records. We want to combine those records by concatenating the BillDocket and showing one result set for each unique EVID.

We want the results to look like the following (again, notice 206 & 207):

view plain print about
1EVID    BillDocket
2191    External:277
3192    External:277
4193    External:277
5194    External:277
6195    Internal:117
7197    Internal:HB2255
8206    Action:123,Action:1321;5445;3545,Combo:1;0;67,External:4;5;7,Internal:1233;1323
9207    External:44;55;66,Internal:11;22;33
10211    External:729
11214    External:481

Compare that to the table above, looking at 206 & 207. We also have 5 less rows in the result set due to the combining of column values.

How do we get this result? Here's the query with some explanations commented inline:

view plain print about
1-- we use the 'WITH' CTE to create the 'anchor' table
2-- also define the temporary table name and columns
3WITH Bills (EVID, BillDocket)
4AS
5(
6-- this is the anchor table upon which we will recurse
7-- if run separately, this query will return the 1st table above
8SELECT J.EVID, BillDocket
9FROM [dbo].JoeTest1 J
10)
11(
12-- this does the recursion
13SELECT Bills2.EVID,
14    -- stuff command, concatenates and combines for us
15    STUFF(
16        -- we are adding the comma plus the next BillDocket to the string
17        (SELECT DISTINCT ',' + J1.BillDocket
18            FROM [dbo].JoeTest1 J1
19            -- matched the anchor to the CTE table
20            WHERE Bills2.[EVID] = J1.[EVID]
21                FOR XML PATH (''))
22            , 1, 1, '')
23        AS BillDocket
24FROM Bills Bills2
25GROUP BY Bills2.EVID
26)
27-- have to keep this to break any type of infinite loop we may cause
28OPTION (MAXRECURSION 1000);

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
Here are 2 MS Technet links that may help with learning about CTEs and Recursion
https://technet.microsoft.com/en-us/library/ms1907...
https://technet.microsoft.com/en-us/library/ms1862...
# Posted By CF Joe | 2/15/17 4:34 PM
BlogCFC was created by Raymond Camden. This blog is running version 5.9.7. Contact Blog Owner