October 16, 2013 at 3:36 am
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.
October 16, 2013 at 4:09 am
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
October 17, 2013 at 12:53 am
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 solutionIF 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
Sorry guys if my message was not that clear and havent put the DDL. 🙁
Cadavre, yes you hit it right! I wanted the result you've posted. But the solution will work for a single record. What if I wanted it on a multiple records?
Change the value of the record to make it more understandable.
DDL:
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN;
DROP TABLE #testEnvironment;
END;
SELECT [Car], [Child], [Parent], [Category_Name]
INTO #testEnvironment
FROM (VALUES('Type R',16369,14499,NULL),
(NULL,14499,14498,'Civic'),
(NULL,14498,14371,'Manual Transmission'),
(NULL,14371,234,'2013'),
(NULL,234,225,'Gasoline'),
(NULL,225,1,'Passenger Car'),
(NULL,1,0,'Honda'),
('SX 2.0',17273,6267,NULL),
(NULL,6267,6265,'CRV'),
(NULL,6265,6264,'Automatic Transmission'),
(NULL,6264,234,'2013'),
(NULL,234,6199,'Diesel'),
(NULL,6199,1,'SUV'),
(NULL,1,0,'Honda')
)a([Car], [Child], [Parent], [Category_Name]);
The result I wanted is:
Car Type Category 1 Category 2 Category 3 Category 4 Category 5 Category6
Type R Civic Manual Transmission 2013 Gasoline Pasenger Car Honda
SX 2.0 CRV Automatic Transmission 2013 Diesel SUV Honda
Basically the records with "NULL" value on [Car] column are the categories and the records with "NULL" values on [Category_Name] are the root.
October 17, 2013 at 3:13 am
ramos.ferdinand (10/17/2013)
Sorry guys if my message was not that clear and havent put the DDL. 🙁Cadavre, yes you hit it right! I wanted the result you've posted. But the solution will work for a single record. What if I wanted it on a multiple records?
Change the value of the record to make it more understandable.
DDL:
IF object_id('tempdb..#testEnvironment') IS NOT NULLBEGIN;
DROP TABLE #testEnvironment;
END;
SELECT [Car], [Child], [Parent], [Category_Name]
INTO #testEnvironment
FROM (VALUES('Type R',16369,14499,NULL),
(NULL,14499,14498,'Civic'),
(NULL,14498,14371,'Manual Transmission'),
(NULL,14371,234,'2013'),
(NULL,234,225,'Gasoline'),
(NULL,225,1,'Passenger Car'),
(NULL,1,0,'Honda'),
('SX 2.0',17273,6267,NULL),
(NULL,6267,6265,'CRV'),
(NULL,6265,6264,'Automatic Transmission'),
(NULL,6264,234,'2013'),
(NULL,234,6199,'Diesel'),
(NULL,6199,1,'SUV'),
(NULL,1,0,'Honda')
)a([Car], [Child], [Parent], [Category_Name]);
The result I wanted is:
Car Type Category 1 Category 2 Category 3 Category 4 Category 5 Category6
Type R Civic Manual Transmission 2013 Gasoline Pasenger Car Honda
SX 2.0 CRV Automatic Transmission 2013 Diesel SUV Honda
Basically the records with "NULL" value on [Car] column are the categories and the records with "NULL" values on [Category_Name] are the root.
There's an error with your hierarchy. Several of your nodes are pointing at multiple areas, which means that they are valid children of both cars. I've changed your hierarchy to this: -
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN;
DROP TABLE #testEnvironment;
END;
SELECT [Car], [Child], [Parent], [Category_Name]
INTO #testEnvironment
FROM (VALUES('Type R',16369,14499,NULL),
(NULL,14499,14498,'Civic'),
(NULL,14498,14371,'Manual Transmission'),
(NULL,14371,234,'2013'),
(NULL,234,225,'Gasoline'),
(NULL,225,1,'Passenger Car'),
(NULL,1,0,'Honda'),
('SX 2.0',17273,6267,NULL),
(NULL,6267,6265,'CRV'),
(NULL,6265,6264,'Automatic Transmission'),
(NULL,6264,235,'2013'),
(NULL,235,6199,'Diesel'),
(NULL,6199,1,'SUV')
)a([Car], [Child], [Parent], [Category_Name]);
DECLARE @sql NVARCHAR(MAX);
WITH CTE AS
(
SELECT [Car], [Child], [Parent], ISNULL([Category_Name],[Car]) AS [Category_Name],
1 AS [Pos]
FROM #testEnvironment
WHERE [Car] IS NOT NULL
UNION ALL
SELECT ISNULL(a.[Car],b.[Car]), a.[Child], a.[Parent], a.[Category_Name], [Pos] + 1
FROM #testEnvironment a
INNER JOIN CTE b ON b.[Parent] = a.[Child]
)
SELECT @sql =
STUFF((SELECT CHAR(13)+CHAR(10)+'UNION ALL SELECT '+Dyn
FROM (SELECT [Car]
FROM CTE
GROUP BY [Car]
)a
CROSS APPLY (SELECT STUFF((SELECT ','+CHAR(13)+CHAR(10)+
CHAR(39)+b.[Category_Name]+CHAR(39)+ ' AS ' +
CASE WHEN b.[Car] = b.[Category_Name]
THEN '[Car Type]'
ELSE '[Category ' +CAST(b.[Pos] AS VARCHAR(3))+']' END
FROM CTE b
WHERE a.[Car] = b.[Car]
ORDER BY [Car], [Pos]
FOR XML PATH(''), TYPE
).value('.','NVARCHAR(MAX)'),1,1,''
)
)ca(Dyn)
FOR XML PATH(''), TYPE
).value('.','NVARCHAR(MAX)'),1,12,''
);
EXECUTE sp_executesql @sql;
The above produces: -
Car Type Category 2 Category 3 Category 4 Category 5 Category 6 Category 7
-------- ---------- ---------------------- ---------- ---------- ------------- ----------
SX 2.0 CRV Automatic Transmission 2013 Diesel SUV Honda
Type R Civic Manual Transmission 2013 Gasoline Passenger Car Honda
I've left a deliberate bug in there for you to try and resolve. What happens if there are different numbers of categories for each car?
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN;
DROP TABLE #testEnvironment;
END;
SELECT [Car], [Child], [Parent], [Category_Name]
INTO #testEnvironment
FROM (VALUES('Type R',16369,14499,NULL),
(NULL,14499,14498,'Civic'),
(NULL,14498,14371,'Manual Transmission'),
(NULL,14371,234,'2013'),
(NULL,234,225,'Gasoline'),
(NULL,225,1,'Passenger Car'),
(NULL,1,0,'Honda'),
('SX 2.0',17273,6267,NULL),
(NULL,6267,6265,'CRV'),
(NULL,6265,6264,'Automatic Transmission'),
(NULL,6264,235,'2013'),
(NULL,235,6199,'Diesel'),
(NULL,6199,1,'SUV'),
('SX 3.0',17273,6267,NULL),
(NULL,6267,6265,'CRV'),
(NULL,6265,6264,'Manual Transmission'),
(NULL,6264,6199,'2013'),
(NULL,6199,1,'SUV')
)a([Car], [Child], [Parent], [Category_Name]);
Note, the SX 3.0 has only 6 categories.
If you execute the code I've supplied again, you get: -
Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
Have a go at solving this yourself. If you run into difficulties, post back with what you've tried and I'll help walk you through the solution.
October 17, 2013 at 8:09 am
Sorry I know it's spoon feeding already. I've exerted all efforts but cant find the right script. I was thinking to put a "NULL" value to the columns that lacks so that i could be able to union. but the problem is i dont know where. :sick:
this is the part that i dont really understand. specially to the part where you put ".value". if you could walk me through on what it does that would be a GREAT HELP.
STUFF((SELECT CHAR(13)+CHAR(10)+'Union All SELECT '+Dyn
FROM (SELECT [book]
FROM CTE
GROUP BY [book]
)a
CROSS APPLY (SELECT STUFF((SELECT ','+CHAR(13)+CHAR(10)+
CHAR(39)+ b.[Category_Name]+CHAR(39)+ ' AS ' +
CASE WHEN b.[book] = b.[Category_Name] THEN '[Book]'
ELSE '[Level ' +CAST(b.[Pos] AS VARCHAR(3))+']'
END
FROM CTE b
WHERE a.[book] = b.[book]
ORDER BY [book], [Pos]
FOR XML PATH(''), TYPE
).value('.','NVARCHAR(MAX)'),1,1,''
)
)ca(Dyn)
FOR XML PATH(''), TYPE
).value('.','NVARCHAR(MAX)'),1,12,''
);
October 17, 2013 at 6:48 pm
ramos.ferdinand (10/17/2013)
DDL:
IF object_id('tempdb..#testEnvironment') IS NOT NULLBEGIN;
DROP TABLE #testEnvironment;
END;
SELECT [Car], [Child], [Parent], [Category_Name]
INTO #testEnvironment
FROM (VALUES('Type R',16369,14499,NULL),
(NULL,14499,14498,'Civic'),
(NULL,14498,14371,'Manual Transmission'),
(NULL,14371,234,'2013'),
(NULL,234,225,'Gasoline'),
(NULL,225,1,'Passenger Car'),
(NULL,1,0,'Honda'),
('SX 2.0',17273,6267,NULL),
(NULL,6267,6265,'CRV'),
(NULL,6265,6264,'Automatic Transmission'),
(NULL,6264,234,'2013'),
(NULL,234,6199,'Diesel'),
(NULL,6199,1,'SUV'),
(NULL,1,0,'Honda')
)a([Car], [Child], [Parent], [Category_Name]);
Using the above sample data, you can unravel the hierarchy using a recursive CTE and then do a cross tab query to PIVOT.
WITH UnravelHiearchy AS
(
SELECT Level=0, Car, Child, Parent, Category_Name
FROM #TestEnvironment
WHERE Car IS NOT NULL
UNION ALL
SELECT Level+1, a.Car, b.Child, b.Parent, b.Category_Name
FROM UnravelHiearchy a
JOIN #TestEnvironment b ON a.Parent = b.Child
)
SELECT Car
,Level1=MAX(CASE WHEN Level = 1 THEN Category_Name END)
,Level2=MAX(CASE WHEN Level = 2 THEN Category_Name END)
,Level3=MAX(CASE WHEN Level = 3 THEN Category_Name END)
,Level4=MAX(CASE WHEN Level = 4 THEN Category_Name END)
,Level5=MAX(CASE WHEN Level = 5 THEN Category_Name END)
,Level6=MAX(CASE WHEN Level = 6 THEN Category_Name END)
FROM UnravelHiearchy
GROUP BY Car
ORDER BY Car;
This way, you'd need to know the maximum number of levels for any car, and add Leveln= up to that level.
The alternative if you don't know the number of levels is that you could insert the unraveled hierarchy into a temp table and then use that along with some dynamic SQL to PIVOT the number of Level columns you need.
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs [/url]
Edit: Sorry to be a bit redundant with what Cadavre posted. No coffee yet this morning so I didn't read it carefully.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 17, 2013 at 7:51 pm
dwain.c it works!!!!!! :Wow:
cadavre i would really appreciate it if you can walk me through on your query. the stuff and xml path are all new to me.
Thanks a lot guys!!! Cheers!
October 17, 2013 at 8:05 pm
Allow me to make amends for my prior transgression with my take on the Dynamic SQL version:
WITH UnravelHiearchy AS
(
SELECT Level=0, Car, Child, Parent, Category_Name
FROM #TestEnvironment
WHERE Car IS NOT NULL
UNION ALL
SELECT Level+1, a.Car, b.Child, b.Parent, b.Category_Name
FROM UnravelHiearchy a
JOIN #TestEnvironment b ON a.Parent = b.Child
)
SELECT Level, Car, Child, Parent, Category_Name
INTO #Temp
FROM UnravelHiearchy;
DECLARE @sql NVARCHAR(MAX);
SELECT @sql = '
SELECT Car' +
(
SELECT ' ,Level' + CAST(n AS VARCHAR) + '=MAX(CASE WHEN Level = ' +
CAST(n AS VARCHAR) + ' THEN Category_name END)'
FROM
(
SELECT TOP
(
(SELECT TOP 1 COUNT(*) FROM #Temp GROUP BY Car ORDER BY 1 DESC) - 1
) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns
) a(n)
ORDER BY n
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'
)
+ ' FROM #Temp
GROUP BY Car
ORDER BY Car;'
--PRINT @sql;
EXEC (@SQL);
GO
DROP TABLE #Temp;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 17, 2013 at 8:07 pm
ramos.ferdinand (10/17/2013)
dwain.c it works!!!!!! :Wow:cadavre i would really appreciate it if you can walk me through on your query. the stuff and xml path are all new to me.
Thanks a lot guys!!! Cheers!
Here's a good article by Wayne Sheffield on using FOR XML PATH to concatenate strings from multiple rows:
Creating a comma-separated list (SQL Spackle) [/url]
Cadavre and I are doing basically the same thing, just a slightly different approach.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 23, 2013 at 5:37 am
ramos.ferdinand (10/17/2013)
dwain.c it works!!!!!! :Wow:cadavre i would really appreciate it if you can walk me through on your query. the stuff and xml path are all new to me.
Thanks a lot guys!!! Cheers!
Sorry, have been massively busy at work. Dwain's method for solving the issue of having different numbers of categories per car is the same that I'd been thinking of and the article he linked on creating a comma separated list will explain what I was doing with the FOR XML PATH stuff. If you need more help, post back.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply