Cadavre (10/16/2013)

---

I don't fully understand what you actually want here, it's always easier to figure out when you supply us with DDL, readily consumable sample data and expected results based on the sample data. Here's my shot in the dark.

First, this is the sample data I used to test with: -

-- Set up readily consumable sample data so that we can test any solution

IF object_id('tempdb..#testEnvironment') IS NOT NULL

BEGIN;

DROP TABLE #testEnvironment;

END;

SELECT [Column 1], [Column 2], [Column 3], [Column 4]

INTO #testEnvironment

FROM (VALUES('Base',16369,14499,NULL),

(NULL,14499,14498,'Level 1'),

(NULL,14498,14371,'Level 2'),

(NULL,14371,234,'Level 3'),

(NULL,234,225,'Level 4'),

(NULL,225,1,'Level 5'),

(NULL,1,0,'Level 6')

)a([Column 1], [Column 2], [Column 3], [Column 4]);

Here's my attempted solution: -

DECLARE @ORDER BIT = 0, @SQL NVARCHAR(MAX);

IF @ORDER = 0

BEGIN;

-- ORDER BASE TO LEAF

WITH CTE AS

(

SELECT [Column 1], [Column 2], [Column 3], ISNULL([Column 4],[Column 1]) AS [Column 4],

1 AS Pos

FROM #testEnvironment

WHERE [Column 1] = 'Base'

UNION ALL

SELECT a.[Column 1], a.[Column 2], a.[Column 3], a.[Column 4], Pos + 1

FROM #testEnvironment a

INNER JOIN CTE b ON b.[Column 3] = a.[Column 2]

)

SELECT @SQL = 'SELECT '+

STUFF((SELECT ','+CHAR(13)+CHAR(10)+

CHAR(39)+[Column 4]+CHAR(39)+' AS [Column '+CAST(Pos AS VARCHAR(3))+']'

FROM CTE

ORDER BY Pos ASC

FOR XML PATH(''),TYPE

).value('.','NVARCHAR(MAX)'),1,1,''

);

END;

ELSE IF @ORDER = 1

BEGIN;

-- ORDER LEAF BASE

WITH CTE AS

(

SELECT [Column 1], [Column 2], [Column 3], ISNULL([Column 4],[Column 1]) AS [Column 4],

1 AS Pos

FROM #testEnvironment

WHERE [Column 1] = 'Base'

UNION ALL

SELECT a.[Column 1], a.[Column 2], a.[Column 3], a.[Column 4], Pos + 1

FROM #testEnvironment a

INNER JOIN CTE b ON b.[Column 3] = a.[Column 2]

)

SELECT @SQL = 'SELECT '+

STUFF((SELECT ','+CHAR(13)+CHAR(10)+

CHAR(39)+[Column 4]+CHAR(39)+' AS [Column '+CAST(Pos AS VARCHAR(3))+']'

FROM CTE

ORDER BY Pos DESC

FOR XML PATH(''),TYPE

).value('.','NVARCHAR(MAX)'),1,1,''

);

END;

EXECUTE sp_executesql @SQL;

That results in: -

Column 1 Column 2 Column 3 Column 4 Column 5 Column 6 Column 7

-------- -------- -------- -------- -------- -------- --------

Base Level 1 Level 2 Level 3 Level 4 Level 5 Level 6

If you change the @ORDER to 1, then it results in: -

Column 7 Column 6 Column 5 Column 4 Column 3 Column 2 Column 1

-------- -------- -------- -------- -------- -------- --------

Level 6 Level 5 Level 4 Level 3 Level 2 Level 1 Base