Linearizing a tree CTE (recursion)

Question

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26984 More actions · Answer 1

Something like this?

DECLARE @t TABLE(name1 VARCHAR(30), type1 VARCHAR(10), name2 VARCHAR(30), type2 VARCHAR(10))

INSERT INTO @t(name1,type1,name2,type2)

SELECT 'Report1','report','table1','table' UNION ALL

SELECT 'Report1','report','view1','view' UNION ALL

SELECT 'report2','report','view2','view' UNION ALL

SELECT 'report2','report','table1','table' UNION ALL

SELECT 'view1','view','table2','table' UNION ALL

SELECT 'view2','view','view3','view' UNION ALL

SELECT 'view3','view','table3','table';

WITH CTE AS (

SELECT name1 AS ReportName,name1,name2,type2

FROM @t

WHERE type1='report'

UNION ALL

SELECT c.ReportName,a.name1,a.name2,a.type2

FROM @t a

INNER JOIN CTE c ON c.name2=a.name1)

SELECT ReportName,name2 AS TableName

FROM CTE

WHERE type2='table'

ORDER BY ReportName,TableName;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

varun.bat Newbie Points: 6 More actions · Answer 2

Thanks a lot Mark

The query is working. I just made a small change to c.TableName=a.ReportName.

regards

Varun