RECURSIVE TABLE

WITH RECURSIVE temp_table (id, name, row_no, depth) AS
(
SELECT
id,
name,
row_no,
depth
FROM
(
SELECT DISTINCT
id,
CAST('' AS VARCHAR(2000)) AS name,
0 AS row_no,
0 AS depth
FROM
TBL_FOR_RECURSIVE_TEST
)tbl
UNION ALL
SELECT
temp_table.id,
temp_table.name || ', ' || base_table.name,
temp_table.row_no + 1 AS row_no,
temp_table.depth + 1 AS newdepth
FROM
temp_table
INNER JOIN
TBL_FOR_RECURSIVE_TEST AS base_table
ON
temp_table.id = base_table.id
AND
temp_table.row_no + 1 = base_table.row_no
WHERE
newdepth < 10
)

SELECT
*
FROM
temp_table
WHERE
(id, row_no) IN (SELECT id,MAX(row_no) FROM temp_table GROUP BY id)
ORDER BY
id

Tweet