July 19, 2012 at 6:51 am
Jeff Moden (7/17/2012)
GSquared (7/17/2012)
He was talking about the last solution I posted. That's a while-loop version.First, apologies for not responding sooner. I've actually be working on a demonstration of what I meant and haven't completed it yet.
Shifting gears to the comment above... Yes and no. Yes, I was talking about a While Loop. No, I wasn't talking about Gus' code. 🙂
Neither the rCTE nor Gus' code actually tells you where the infinite loop is. Gus has a nice bypass in his code for such anomolies but I'm not sure that you want a bypass. I'd think that you'd want to identify the problem in the hierarchy and fix it.
True. If you want to find them, you need to actually build something recursive that separately builds each level of the hierarchy and checks for rows that were already added, and then concatenates the rows together into a final dataset, once it's validated all of it.
Since it's dynamic, and temp tables and table variables can't scope into that kind of thing easily, the easiest way I know of to do that in T-SQL is to build a recursive XML object that scans itself up and down. Alternatively, if you're in SQL 2008 or later (which you appear to be based on the forum this question is in), you can build a HierarchyID path and use that to check for any object that is its own parent. That's more complex, but also works.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 23, 2012 at 2:43 am
GSquared (7/19/2012)
Jeff Moden (7/17/2012)
GSquared (7/17/2012)
He was talking about the last solution I posted. That's a while-loop version.First, apologies for not responding sooner. I've actually be working on a demonstration of what I meant and haven't completed it yet.
Shifting gears to the comment above... Yes and no. Yes, I was talking about a While Loop. No, I wasn't talking about Gus' code. 🙂
Neither the rCTE nor Gus' code actually tells you where the infinite loop is. Gus has a nice bypass in his code for such anomolies but I'm not sure that you want a bypass. I'd think that you'd want to identify the problem in the hierarchy and fix it.
True. If you want to find them, you need to actually build something recursive that separately builds each level of the hierarchy and checks for rows that were already added, and then concatenates the rows together into a final dataset, once it's validated all of it.
Since it's dynamic, and temp tables and table variables can't scope into that kind of thing easily, the easiest way I know of to do that in T-SQL is to build a recursive XML object that scans itself up and down. Alternatively, if you're in SQL 2008 or later (which you appear to be based on the forum this question is in), you can build a HierarchyID path and use that to check for any object that is its own parent. That's more complex, but also works.
There's also another way using the rCTE. Simply add a limiter on the recursive leg. Meaning don't let it recurse (is that a word?) past the limiter (use a parameter for this). Run the rCTE, increasing the limiter one increment at a time until the rCTE chokes on the result. You can then use the prior result set to work forward to through the next recursion level manually to identify the anomaly.
Sounds time consuming I know, but if Jeff is right and there aren't many levels, it shouldn't be too bad.
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
July 23, 2012 at 6:40 am
dwain.c (7/23/2012)
GSquared (7/19/2012)
Jeff Moden (7/17/2012)
GSquared (7/17/2012)
He was talking about the last solution I posted. That's a while-loop version.First, apologies for not responding sooner. I've actually be working on a demonstration of what I meant and haven't completed it yet.
Shifting gears to the comment above... Yes and no. Yes, I was talking about a While Loop. No, I wasn't talking about Gus' code. 🙂
Neither the rCTE nor Gus' code actually tells you where the infinite loop is. Gus has a nice bypass in his code for such anomolies but I'm not sure that you want a bypass. I'd think that you'd want to identify the problem in the hierarchy and fix it.
True. If you want to find them, you need to actually build something recursive that separately builds each level of the hierarchy and checks for rows that were already added, and then concatenates the rows together into a final dataset, once it's validated all of it.
Since it's dynamic, and temp tables and table variables can't scope into that kind of thing easily, the easiest way I know of to do that in T-SQL is to build a recursive XML object that scans itself up and down. Alternatively, if you're in SQL 2008 or later (which you appear to be based on the forum this question is in), you can build a HierarchyID path and use that to check for any object that is its own parent. That's more complex, but also works.
There's also another way using the rCTE. Simply add a limiter on the recursive leg. Meaning don't let it recurse (is that a word?) past the limiter (use a parameter for this). Run the rCTE, increasing the limiter one increment at a time until the rCTE chokes on the result. You can then use the prior result set to work forward to through the next recursion level manually to identify the anomaly.
Sounds time consuming I know, but if Jeff is right and there aren't many levels, it shouldn't be too bad.
The problem with something like that is that it solves the problem this time, but:
a) It solves a symptom, not the disease (which is bad data that should be prevented at the point of entry)
b) It's going to be manual, since it involves watching till something blows up, then fixing that, so if it comes up again you have to manually run it again
You're going to be better off building an error trap that takes rows already appearing higher in the hierarchy and pulling them off to the side and raising an automatic alert to the business-users responsible for the data, or building custom rules into the query to allow Joe to be his own boss (or whatever the situation is), so that it complies with local policy.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 23, 2012 at 7:34 am
GSquared (7/23/2012)
dwain.c (7/23/2012)
GSquared (7/19/2012)
Jeff Moden (7/17/2012)
GSquared (7/17/2012)
He was talking about the last solution I posted. That's a while-loop version.First, apologies for not responding sooner. I've actually be working on a demonstration of what I meant and haven't completed it yet.
Shifting gears to the comment above... Yes and no. Yes, I was talking about a While Loop. No, I wasn't talking about Gus' code. 🙂
Neither the rCTE nor Gus' code actually tells you where the infinite loop is. Gus has a nice bypass in his code for such anomolies but I'm not sure that you want a bypass. I'd think that you'd want to identify the problem in the hierarchy and fix it.
True. If you want to find them, you need to actually build something recursive that separately builds each level of the hierarchy and checks for rows that were already added, and then concatenates the rows together into a final dataset, once it's validated all of it.
Since it's dynamic, and temp tables and table variables can't scope into that kind of thing easily, the easiest way I know of to do that in T-SQL is to build a recursive XML object that scans itself up and down. Alternatively, if you're in SQL 2008 or later (which you appear to be based on the forum this question is in), you can build a HierarchyID path and use that to check for any object that is its own parent. That's more complex, but also works.
There's also another way using the rCTE. Simply add a limiter on the recursive leg. Meaning don't let it recurse (is that a word?) past the limiter (use a parameter for this). Run the rCTE, increasing the limiter one increment at a time until the rCTE chokes on the result. You can then use the prior result set to work forward to through the next recursion level manually to identify the anomaly.
Sounds time consuming I know, but if Jeff is right and there aren't many levels, it shouldn't be too bad.
The problem with something like that is that it solves the problem this time, but:
a) It solves a symptom, not the disease (which is bad data that should be prevented at the point of entry)
b) It's going to be manual, since it involves watching till something blows up, then fixing that, so if it comes up again you have to manually run it again
You're going to be better off building an error trap that takes rows already appearing higher in the hierarchy and pulling them off to the side and raising an automatic alert to the business-users responsible for the data, or building custom rules into the query to allow Joe to be his own boss (or whatever the situation is), so that it complies with local policy.
I agree that it is tedious and unwarranted if you can catch it in the front end (and you should try to do so). But if you're stuck with an existing record set with data flaws in it, your options are limited. Unless you want to try to build a query that may find some or all of the flaws directly. Possible but may not be easy.
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
July 23, 2012 at 4:33 pm
^^^ Too much text for me to read right now 😉 But here is a solution using Hierarchyid just for the heck of it. I'm not the best at writing recursive CTEs but speed with HeirachyIDs with the proper code technique and indexes is as fast as it gets IMO.
*Edits*
After reading the thread, yes the data must be "clean" and no loops. I included logic to prevent looping.
DECLARE @TEMP AS TABLE
(Orgunitcode INT, Childorgunitcode INT)
INSERT INTO @TEMP
SELECT 1123, 1256 UNION ALL
SELECT 1256, 1345 UNION ALL
SELECT 1345, 1489
--loop test
--UNION ALL
--SELECT 1256, 1345 UNION ALL
--SELECT 1256, 1489
DECLARE @hiertemp TABLE(parent INT,child INT, hid hierarchyid,level INT)
;WITH rcte
AS
(
SELECT
parent = CAST(NULL AS INT),
child = Orgunitcode,
hid = CAST(N'/' AS VARCHAR(MAX)),
level = 0
FROM @temp t1
WHERE Orgunitcode NOT IN (SELECT Childorgunitcode FROM @TEMP) --parent records
UNION ALL
SELECT
t.Orgunitcode
,t.Childorgunitcode
,CAST(r1.hid + CAST(t.Orgunitcode AS NVARCHAR(10)) + '/' AS VARCHAR(MAX))--
,level + 1
FROM rcte r1 INNER JOIN @TEMP t ON t.Orgunitcode = r1.child
AND r1.hid NOT LIKE '%/'+CAST(t.Orgunitcode AS NVARCHAR(10))+'/%'--Will only loop thru once..
)
INSERT @hiertemp
SELECT DISTINCT * FROM rcte
OPTION(MAXRECURSION 0)
SELECT p.child,c.child,p.hid.ToString(),c.hid.ToString()
FROM @hiertemp p INNER JOIN @hiertemp c ON c.hid.IsDescendantOf(p.hid) = 1 AND c.level > p.level
ORDER BY p.child,c.child
July 23, 2012 at 7:29 pm
SQL Padawan (7/23/2012)
^^^ Too much text for me to read right now 😉 But here is a solution using Hierarchyid just for the heck of it. I'm not the best at writing recursive CTEs but speed with HeirachyIDs with the proper code technique and indexes is as fast as it gets IMO.
With that thought in mind, I know several different ways to do it but I'd love to see a HIERARCHYID example that counts the number of people from current node to leaf nodes for all nodes in the hierarchy by relative level along with a sum of all the sales of those people also by relative level. If you know how, I can even provide the hierarchy...
/**********************************************************************************************
Create an "Adjacency List" Hierarchical Model
**********************************************************************************************/
--===== Since we're going to drop and create tables, do this in a nice
-- safe place that everyone has.
USE TempDB
;
--===== Conditionally drop Temp tables to make reruns easy
IF OBJECT_ID('dbo.Employee','U') IS NOT NULL
DROP TABLE dbo.Employee
;
--===== Create the test table with a clustered PK
CREATE TABLE dbo.Employee
(
EmployeeID INT NOT NULL,
ManagerID INT NULL,
EmployeeName VARCHAR(10) NOT NULL,
Sales INT NOT NULL,
CONSTRAINT PK_Employee PRIMARY KEY CLUSTERED (EmployeeID ASC),
CONSTRAINT FK_Employee_Employee FOREIGN KEY (ManagerID) REFERENCES dbo.Employee (EmployeeID)
)
;
--===== Populate the test table with test data.
-- Since each row forms a parent/child relationship,
-- it's an "Adjacency Model
INSERT INTO dbo.Employee
(EmployeeID, ManagerID, EmployeeName, Sales)
SELECT 1,NULL,'Jim' ,200000 UNION ALL
SELECT 2, 1,'Lynne' , 90000 UNION ALL
SELECT 3, 1,'Bob' ,100000 UNION ALL
SELECT 6, 17,'Eric' , 75000 UNION ALL
SELECT 8, 3,'Bill' , 80000 UNION ALL
SELECT 7, 3,'Vivian' , 60000 UNION ALL
SELECT 12, 8,'Megan' , 50000 UNION ALL
SELECT 13, 8,'Kim' , 55000 UNION ALL
SELECT 17, 2,'Butch' , 70000 UNION ALL
SELECT 18, 39,'Lisa' , 40000 UNION ALL
SELECT 20, 3,'Natalie', 40000 UNION ALL
SELECT 21, 39,'Homer' , 30000 UNION ALL
SELECT 39, 1,'Ken' , 90000 UNION ALL
SELECT 40, 1,'Marge' ,120000
;
--===== Display the data in the Employee table
SELECT *
FROM dbo.Employee
ORDER BY EmployeeID
;
CREATE INDEX IX_Employee_Composite01
ON dbo.Employee (ManagerID, EmployeeID, EmployeeName)
;
-- INSERT INTO dbo.Employee
-- (EmployeeID, ParentID, EmployeeName, Sales)
--SELECT 72,8,'Charlie' , 85000
Here are the expected results. "RelativeNodeLevel = 0" is the grand total for the given employee.
EmployeeID RelativeNodeLevel HierarchyLevel NodesInDownLine Sales
----------- ----------------- -------------- --------------- ---------------------
1 1 1 1 200000.00
1 2 2 4 400000.00
1 3 3 6 320000.00
1 4 4 3 180000.00
1 0 1 14 1100000.00
2 1 2 1 90000.00
2 2 3 1 70000.00
2 3 4 1 75000.00
2 0 2 3 235000.00
3 1 2 1 100000.00
3 2 3 3 180000.00
3 3 4 2 105000.00
3 0 2 6 385000.00
6 1 4 1 75000.00
6 0 4 1 75000.00
7 1 3 1 60000.00
7 0 3 1 60000.00
8 1 3 1 80000.00
8 2 4 2 105000.00
8 0 3 3 185000.00
12 1 4 1 50000.00
12 0 4 1 50000.00
13 1 4 1 55000.00
13 0 4 1 55000.00
17 1 3 1 70000.00
17 2 4 1 75000.00
17 0 3 2 145000.00
18 1 3 1 40000.00
18 0 3 1 40000.00
20 1 3 1 40000.00
20 0 3 1 40000.00
21 1 3 1 30000.00
21 0 3 1 30000.00
39 1 2 1 90000.00
39 2 3 2 70000.00
39 0 2 3 160000.00
40 1 2 1 120000.00
40 0 2 1 120000.00
I included logic to prevent looping.
Again, I strongly caution against doing such a thing unless you're trying to resolve a "web" problem, such as the Traveling Salesman problem, because you can very definitely be missing a serious amount of data if you prevent the looping by code rather than by correcting the data.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 23, 2012 at 9:44 pm
Jeff Moden (7/23/2012)
SQL Padawan (7/23/2012)
^^^ Too much text for me to read right now 😉 But here is a solution using Hierarchyid just for the heck of it. I'm not the best at writing recursive CTEs but speed with HeirachyIDs with the proper code technique and indexes is as fast as it gets IMO.With that thought in mind, I know several different ways to do it but I'd love to see a HIERARCHYID example that counts the number of people from current node to leaf nodes for all nodes in the hierarchy by relative level along with a sum of all the sales of those people also by relative level. If you know how, I can even provide the hierarchy...
/**********************************************************************************************Create an "Adjacency List" Hierarchical Model
**********************************************************************************************/
--===== Since we're going to drop and create tables, do this in a nice
-- safe place that everyone has.
USE TempDB
;
--===== Conditionally drop Temp tables to make reruns easy
IF OBJECT_ID('dbo.Employee','U') IS NOT NULL
DROP TABLE dbo.Employee
;
--===== Create the test table with a clustered PK
CREATE TABLE dbo.Employee
(
EmployeeID INT NOT NULL,
ManagerID INT NULL,
EmployeeName VARCHAR(10) NOT NULL,
Sales INT NOT NULL,
CONSTRAINT PK_Employee PRIMARY KEY CLUSTERED (EmployeeID ASC),
CONSTRAINT FK_Employee_Employee FOREIGN KEY (ManagerID) REFERENCES dbo.Employee (EmployeeID)
)
;
--===== Populate the test table with test data.
-- Since each row forms a parent/child relationship,
-- it's an "Adjacency Model
INSERT INTO dbo.Employee
(EmployeeID, ManagerID, EmployeeName, Sales)
SELECT 1,NULL,'Jim' ,200000 UNION ALL
SELECT 2, 1,'Lynne' , 90000 UNION ALL
SELECT 3, 1,'Bob' ,100000 UNION ALL
SELECT 6, 17,'Eric' , 75000 UNION ALL
SELECT 8, 3,'Bill' , 80000 UNION ALL
SELECT 7, 3,'Vivian' , 60000 UNION ALL
SELECT 12, 8,'Megan' , 50000 UNION ALL
SELECT 13, 8,'Kim' , 55000 UNION ALL
SELECT 17, 2,'Butch' , 70000 UNION ALL
SELECT 18, 39,'Lisa' , 40000 UNION ALL
SELECT 20, 3,'Natalie', 40000 UNION ALL
SELECT 21, 39,'Homer' , 30000 UNION ALL
SELECT 39, 1,'Ken' , 90000 UNION ALL
SELECT 40, 1,'Marge' ,120000
;
--===== Display the data in the Employee table
SELECT *
FROM dbo.Employee
ORDER BY EmployeeID
;
CREATE INDEX IX_Employee_Composite01
ON dbo.Employee (ManagerID, EmployeeID, EmployeeName)
;
-- INSERT INTO dbo.Employee
-- (EmployeeID, ParentID, EmployeeName, Sales)
--SELECT 72,8,'Charlie' , 85000
Here are the expected results. "RelativeNodeLevel = 0" is the grand total for the given employee.
EmployeeID RelativeNodeLevel HierarchyLevel NodesInDownLine Sales
----------- ----------------- -------------- --------------- ---------------------
1 1 1 1 200000.00
1 2 2 4 400000.00
1 3 3 6 320000.00
1 4 4 3 180000.00
1 0 1 14 1100000.00
2 1 2 1 90000.00
2 2 3 1 70000.00
2 3 4 1 75000.00
2 0 2 3 235000.00
3 1 2 1 100000.00
3 2 3 3 180000.00
3 3 4 2 105000.00
3 0 2 6 385000.00
6 1 4 1 75000.00
6 0 4 1 75000.00
7 1 3 1 60000.00
7 0 3 1 60000.00
8 1 3 1 80000.00
8 2 4 2 105000.00
8 0 3 3 185000.00
12 1 4 1 50000.00
12 0 4 1 50000.00
13 1 4 1 55000.00
13 0 4 1 55000.00
17 1 3 1 70000.00
17 2 4 1 75000.00
17 0 3 2 145000.00
18 1 3 1 40000.00
18 0 3 1 40000.00
20 1 3 1 40000.00
20 0 3 1 40000.00
21 1 3 1 30000.00
21 0 3 1 30000.00
39 1 2 1 90000.00
39 2 3 2 70000.00
39 0 2 3 160000.00
40 1 2 1 120000.00
40 0 2 1 120000.00
I included logic to prevent looping.
Again, I strongly caution against doing such a thing unless you're trying to resolve a "web" problem, such as the Traveling Salesman problem, because you can very definitely be missing a serious amount of data if you prevent the looping by code rather than by correcting the data.
Here is a "quick" answer with a group by.
--Add hierarchyid column
ALTER TABLE dbo.employee
ADD hid hierarchyid
--Add level column
ALTER TABLE dbo.employee
ADD hidlevel AS hid.GetLevel()
--Build hierachy
;WITH rCTE
AS (
SELECT EmployeeID,ManagerID,Sales,level = CAST(0 AS INT),hid = CAST('/' AS NVARCHAR(MAX))
FROM dbo.employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID,e.ManagerID,e.Sales,level+1,CAST(r.hid +CAST(e.EmployeeID AS NVARCHAR(10))+'/' AS NVARCHAR(MAX))
FROM rCTE r INNER JOIN dbo.Employee e ON r.EmployeeID = e.ManagerID
)
UPDATE e
SET e.hid = r.hid--Update table
FROM dbo.Employee e
INNER JOIN rCTE r ON e.EmployeeID = r.EmployeeID
--Cover the query
CREATE INDEX IX_HIERARCHYINDEX ON dbo.employee(hid,hidlevel,employeeid) INCLUDE (sales)
--Pull back the data
SELECT
p.employeeid,
RelativeNodeLevel = ROW_NUMBER() OVER (PARTITION BY p.employeeid ORDER BY c.hidlevel) ,
HierarchyLevel = c.hidlevel+1,NodesInDownLine = COUNT(c.employeeid),Sales = SUM(c.sales)
FROM dbo.Employee p INNER JOIN dbo.Employee c ON c.hid.IsDescendantOf(p.hid) = 1 AND p.hidlevel <= c.hidLevel
GROUP BY p.employeeid,c.hidlevel
UNION ALL
SELECT p.employeeid,0,1,COUNT(c.employeeid),SUM(c.sales)
FROM dbo.Employee p INNER JOIN dbo.Employee c ON c.hid.IsDescendantOf(p.hid) = 1 AND p.hidlevel <= c.hidLevel
GROUP BY p.employeeid
ORDER BY p.employeeid
Stats:
(38 row(s) affected)
Table 'Employee'. Scan count 30, logical reads 60, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 4 ms.
Not too happy with the 30 scans...I'll work on another solution when I have some more free time.
July 24, 2012 at 2:50 am
Hi
Apologies I've had my head down trying to get this solution put into place
There are some interesting approaches on this thread and I would like to ask for your help on a another unusual puzzle (on the same topic) if I may..
The requirements have changed, the table was not fit for purpose, consider the following:
DECLARE @TEMP AS TABLE
(Orgunitcode INT, Childorgunitcode INT)
INSERT INTO @TEMP
SELECT 1, 2 UNION ALL
SELECT 2, 3 UNION ALL
SELECT 3, 4 UNION ALL
SELECT 4, 5 UNION ALL
SELECT 1, 6
-- Expected Results
SELECT 1, 2, 3, 4, 5 UNION ALL
SELECT 1, 6, NULL, NULL, NULL UNION ALL
SELECT 1, 3, 4, 5, NULL UNION ALL
SELECT 1, 4, 5, NULL, NULL UNION ALL
SELECT 1, 5, NULL, NULL, NULL UNION ALL
SELECT 2, 3, 4, 5, NULL UNION ALL
SELECT 2, 4, 5, NULL, NULL UNION ALL
SELECT 2, 5, NULL, NULL, NULL UNION ALL
SELECT 3, 4, 5, NULL, NULL UNION ALL
SELECT 3, 5, NULL, NULL, NULL UNION ALL
SELECT 4, 5, NULL, NULL, NULL UNION ALL
SELECT 5, NULL, NULL, NULL, NULL
The reason for the table in this structure is for a BOBJ reporting structure, without going into too much depth when the user drills through it looks at the Orgunit and then presents a selection so at the top most level in Orgunit 1 the user is presented with all orgunits, they then drill into 3 and are only presented with 4 & 5 and so on.
I know that there are better ways of structuring this table but apparently it is a requirement for this particular install..
The data here is overly simplified but as you can see 6 & 2 are both direct descendants of 1 therefore there are two rows for these.
In the actual data there may be many permutations in levels 2 - 10 (only a maximum of 10 however)
Been scratching my head over this one and would really appreciate some help!
Cheers
Edit: Expected results were incorrect, thanks Jeff 🙂
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
July 24, 2012 at 6:46 am
I guess I just don't understand what the connection is between 1,6 and 6,3 is. For that matter, I don't see 6 connecting to anything.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 24, 2012 at 7:26 am
Jeff Moden (7/24/2012)
I guess I just don't understand what the connection is between 1,6 and 6,3 is. For that matter, I don't see 6 connecting to anything.
Hi Jeff
Thanks for taking a look
You have raised a very good point there, thank-you for spotting it!
There is no connection between 6 and 3 that was a mistake on my part pulling the data together ( I will edit my post now)
However the connection between 1 & 6 still stands, 6 is a descendant of 1 however there will be no more descendants of 6 since thats where the hierarchy for that branch ends..
So the Hierarchy is as follows (in this simple example)
1
/ 2 6
/
3
/
4
/ \
5 7
So I need to traverse the structure upwards giving a row for each result with all the parents in a separate column) until I reach the top most parent of that branch so if 4 had 2 children it would show the parents path from 5 to 1 (in 1 row) and 7 to 1 (in another row) (passing through 4) then rows showing the path 4 - 1 then 3 - 1 then 2 - 1 and finally 1 (which will have no path as its the parent so all columns except the first will show NULL)
Then I need to step one parent down so the result set would show the parents path from 5 to 2 (in 1 row) and 7 to 2 (in another row) (passing through 4) then rows showing the path 4 - 2 then 3 - 2 then finally 2 (which will have no path as its the parent so all columns except the first will show NULL)
Does that make sense Jeff?
Also apologies at my awful attempt at a diagram!
Cheers
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
July 24, 2012 at 7:53 am
It does make sense, Andy. Thank you for the additional information.
What you're looking for is called a "materialized hierarchical path" and it's actually pretty simple to make one using an rCTE. Do you want the path in a single delimited column or do you need multiple columns as you first indicated?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 24, 2012 at 7:56 am
I'm also not so sure about the following...
(which will have no path as its the parent so all columns except the first will show NULL)
Are you sure that's not backwards?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 24, 2012 at 8:12 am
Yes I think your right, I'm still trying to get my head around this as have never had to deal with this kind of data..
Consider my posted result for Parent 1:
ParentL1L2L3L4
12345
1345NULL
145NULLNULL
15NULLNULLNULL
16NULLNULLNULL
the table is traversing the path back on itself - the top most row shows the full path from parent (1) to the lower most Child (5), then the next row is traversing the path (still from the parent 1) but from 3 - 5 and so on until we reach the lower most child 5.
6 is on its own with no path as its a direct descendant of 1 with no children
Is that what you mean by materialized hierarchical path?? If so then Yes!!! 🙂
Edit: Yes I need the results in separate columns I'll be going down to a max of 10 levels
Thank you Jeff
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
July 24, 2012 at 8:45 am
I really am having fun with hierarchyids but here is a solution assuming there are only 5 levels. 😛
I had to tweak the code I wrote earlier.
DECLARE @TEMP AS TABLE
(Orgunitcode INT, Childorgunitcode INT)
INSERT INTO @TEMP
SELECT 1, 2 UNION ALL
SELECT 2, 3 UNION ALL
SELECT 3, 4 UNION ALL
SELECT 4, 5 UNION ALL
SELECT 1, 6
DECLARE @hiertemp TABLE(parent INT,child INT, hid hierarchyid,level INT)
;WITH rcte
AS
(
SELECT
parent = CAST(NULL AS INT),
child = Orgunitcode,
hid = CAST(N'/'+CAST(DENSE_RANK() OVER (ORDER BY Orgunitcode) AS VARCHAR(10))+N'/' AS VARCHAR(MAX)),
level = 0
FROM @temp t1
WHERE Orgunitcode NOT IN (SELECT Childorgunitcode FROM @TEMP) --parent records
UNION ALL
SELECT
t.Orgunitcode
,t.Childorgunitcode
,CAST(r1.hid + CAST(DENSE_RANK() OVER (ORDER BY Childorgunitcode) AS VARCHAR(10)) + '/' AS VARCHAR(MAX))--
,level + 1
FROM rcte r1 INNER JOIN @TEMP t ON t.Orgunitcode = r1.child
--AND r1.hid NOT LIKE '%/'+CAST(t.Orgunitcode AS NVARCHAR(10))+'/%'--Will only loop thru once..
)
INSERT @hiertemp
SELECT DISTINCT * FROM rcte
OPTION(MAXRECURSION 0)
SELECT p.child,c.child,c2.child,c3.child,c4.child
FROM @hiertemp p LEFT JOIN @hiertemp c ON c.hid.IsDescendantOf(p.hid) = 1 AND c.level > p.level
LEFT JOIN @hiertemp c2 ON c2.hid.IsDescendantOf(c.hid) = 1 AND c2.level > c.level
LEFT JOIN @hiertemp c3 ON c2.hid.IsDescendantOf(c.hid) = 1 AND c3.level > c2.level
LEFT JOIN @hiertemp c4 ON c2.hid.IsDescendantOf(c.hid) = 1 AND c4.level > c3.level
WHERE p.level = 0
ORDER BY p.child,c.child
July 24, 2012 at 9:02 am
SQL Padawan (7/24/2012)
I really am having fun with hierarchyids but here is a solution assuming there are only 5 levels. 😛I had to tweak the code I wrote earlier.
DECLARE @TEMP AS TABLE
(Orgunitcode INT, Childorgunitcode INT)
INSERT INTO @TEMP
SELECT 1, 2 UNION ALL
SELECT 2, 3 UNION ALL
SELECT 3, 4 UNION ALL
SELECT 4, 5 UNION ALL
SELECT 1, 6
DECLARE @hiertemp TABLE(parent INT,child INT, hid hierarchyid,level INT)
;WITH rcte
AS
(
SELECT
parent = CAST(NULL AS INT),
child = Orgunitcode,
hid = CAST(N'/'+CAST(DENSE_RANK() OVER (ORDER BY Orgunitcode) AS VARCHAR(10))+N'/' AS VARCHAR(MAX)),
level = 0
FROM @temp t1
WHERE Orgunitcode NOT IN (SELECT Childorgunitcode FROM @TEMP) --parent records
UNION ALL
SELECT
t.Orgunitcode
,t.Childorgunitcode
,CAST(r1.hid + CAST(DENSE_RANK() OVER (ORDER BY Childorgunitcode) AS VARCHAR(10)) + '/' AS VARCHAR(MAX))--
,level + 1
FROM rcte r1 INNER JOIN @TEMP t ON t.Orgunitcode = r1.child
--AND r1.hid NOT LIKE '%/'+CAST(t.Orgunitcode AS NVARCHAR(10))+'/%'--Will only loop thru once..
)
INSERT @hiertemp
SELECT DISTINCT * FROM rcte
OPTION(MAXRECURSION 0)
SELECT p.child,c.child,c2.child,c3.child,c4.child
FROM @hiertemp p LEFT JOIN @hiertemp c ON c.hid.IsDescendantOf(p.hid) = 1 AND c.level > p.level
LEFT JOIN @hiertemp c2 ON c2.hid.IsDescendantOf(c.hid) = 1 AND c2.level > c.level
LEFT JOIN @hiertemp c3 ON c2.hid.IsDescendantOf(c.hid) = 1 AND c3.level > c2.level
LEFT JOIN @hiertemp c4 ON c2.hid.IsDescendantOf(c.hid) = 1 AND c4.level > c3.level
WHERE p.level = 0
ORDER BY p.child,c.child
Thank-you SQL Padawan
The results are close but there are a few discrepancies 🙁
However this is not the worst news, when I originally posted this topic I was told this was a 2008 instance, now been told its 2005 so can't use this datatype 🙁
Thanks again
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply