Is there a set-based solution for executing an rCTE over multiple rows?

Question

I have a table that stores a number of rows, each declaring a point (x, y). Each row references the next point in a pattern, so the query to retrieve an entire pattern is recursively defined through a linked list structure. I've developed a rCTE to retrieve a full structure that looks like this...

WITH list (id, next, x, y) AS ( 
  -- rCTE anchor expression
  SELECT id, next, x, y 
  FROM POINT 
  WHERE id = @id 
  UNION ALL 

  -- rCTE recursive expression
  SELECT POINT.id, POINT.next, POINT.x, POINT.y 
  FROM POINT 
  INNER JOIN list ON POINT.id = list.next) 

SELECT x, y FROM list

Additionally, I have another table that references the starting point for each of these patterns stored in the above-described table.

The end goal is to develop a query that will return a 2-dimensional result with all of the points for each pattern consolidated into a comma-delimited list. This return value would look something like this:

id | name | points
-----------------------
1  | test | 1,2,1,3,1,4

(The odd-number indices in the points are x and the even are y.)

Now the naive solution to achieve this is to loop the parent table and for each of the parent table rows set @id and run the rCTE. That would work and I have a rough implementation of this already, but I don't like this solution. Is there a better, more "SQL" way to achieve this goal?

score 0 · Accepted Answer · answered Nov 05 '14 at 23:45

Below is the solution I came up with, where "AREA" is the parent table which references the beginning point in a given area.

WITH list (id, next, latitude, longitude, path) AS (  
    -- rCTE anchor expression
    SELECT POINT.id, next, latitude, longitude, path = 1
    FROM AREA INNER JOIN POINT ON AREA."start" = POINT.ID  
    UNION ALL  

    -- rCTE recursive expression
    SELECT POINT.id, POINT.next, POINT.latitude, POINT.longitude, list.path + 1
    FROM POINT INNER JOIN list ON POINT.id = list.next)  

SELECT a.name, points = STUFF(  
    (SELECT ',' + CAST(latitude AS VARCHAR) + ',' + CAST(longitude AS VARCHAR)  
        FROM list b  
        WHERE a.name = b.name  
        ORDER BY b.path ASC  
        FOR XML PATH('')),  
        1, 1, '')  
FROM list a

Is there a set-based solution for executing an rCTE over multiple rows?

1 Answers1