ramos.ferdinand (10/16/2013)
Hi guys,I'm trying to create an hierarchy using tsql. Below is a sample data:
Column 1 Column2 Column3 Column4
Base 1636914499NULL
NULL 1449914498Level1
NULL 1449814371Level2
NULL 14371234 Level3
NULL 234 225 Level4
NULL 225 1 Level5
NULL 1 0 Level6
Notice Column3 is the child of the root(Base) but will be the parent of the next level.
The result I want to achieve is
Column1 Column2 Column3 Column4 Column5 Column6 Column7
Base Level 1 Level 2 Level 3 Level 4 Level 5 Level 6
or
Column1 Column2 Column3 Column4 Column5 Column6 Column7
Level 6 Level 5 Level 4 Level 3 Level 2 Level 1 Base
Any idea?
Thanks in advance.
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