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:
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):
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:
-- also define the temporary table name and columns
WITH Bills (EVID, BillDocket)
-- this is the anchor table upon which we will recurse
-- if run separately, this query will return the 1st table above
SELECT J.EVID, BillDocket
FROM [dbo].JoeTest1 J
-- this does the recursion
-- stuff command, concatenates and combines for us
-- we are adding the comma plus the next BillDocket to the string
(SELECT DISTINCT ',' + J1.BillDocket
FROM [dbo].JoeTest1 J1
-- matched the anchor to the CTE table
WHERE Bills2.[EVID] = J1.[EVID]
FOR XML PATH (''))
, 1, 1, '')
FROM Bills Bills2
GROUP BY Bills2.EVID
-- have to keep this to break any type of infinite loop we may cause
OPTION (MAXRECURSION 1000);
-- we use the 'WITH' CTE to create the 'anchor' table