July 30, 2018 at 2:06 am
i have some data which has a variable amount of hiearchy levels to it, DAX has a function called Path(), which gets the full path of the item i.e Path(id, parentid), what is the best way to get the levels in SQL, the id's are text fields and its assetnum and parentum.
I know you can do do self joins but that is such a convoluted and labourious way, does sql have any functions that can help do this more dynaically, like the DAX method?
July 30, 2018 at 3:35 am
You can use a recursive query to get the levels for heirarchical data:
USE [tempdb]
GO
-- Test data:
DROP TABLE dbo.Data1;
CREATE TABLE dbo.Data1 (Assetnum Char(6), Parentnum Char(6))
INSERT INTO dbo.Data1 VALUES ( '1', '')
INSERT INTO dbo.Data1 VALUES ( '2', '1')
INSERT INTO dbo.Data1 VALUES ( '3', '1')
INSERT INTO dbo.Data1 VALUES ( '4', '3')
INSERT INTO dbo.Data1 VALUES ( '5', '4')
INSERT INTO dbo.Data1 VALUES ( '6', '4')
INSERT INTO dbo.Data1 VALUES ( '7', '3')
INSERT INTO dbo.Data1 VALUES ( '8', '4')
-- Recursive CTE:
;WITH CTE AS (
SELECT Assetnum, Parentnum
, 0 AS [Level]
FROM dbo.Data1
WHERE Assetnum = 1
UNION ALL
SELECT d.Assetnum, d.Parentnum
, [Level] + 1
FROM dbo.Data1 d
INNER JOIN CTE cte ON cte.Assetnum = d.Parentnum
)
SELECT *
FROM CTE
ORDER BY [Level], Assetnum
July 30, 2018 at 5:22 am
Regarding the example above: a quick reminder that the WITH statement does not need to start with a semicolon. All that is required is that the statement which precedes it (if there is one) be terminated by a semicolon.
If you are lucky enough to have SQL Prompt installed, it can fill in the semicolon terminators for you.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 30, 2018 at 7:07 am
That's true - it is a bit scrappy.
Tidied up:
USE [tempdb];
GO
-- Test data:
IF OBJECT_ID('dbo.Data1') IS NOT NULL DROP TABLE dbo.Data1;
CREATE TABLE dbo.Data1 (Assetnum Char(6), Parentnum Char(6));
INSERT INTO dbo.Data1 VALUES ( '1', ''),
( '2', '1'),
( '3', '1'),
( '4', '3'),
( '5', '4'),
( '6', '4'),
( '7', '2'),
( '8', '7'),
( '9', '8');
-- Recursive CTE:
WITH CTE AS (
SELECT Assetnum, Parentnum
, 0 AS [Level]
FROM dbo.Data1
WHERE Assetnum = 1
UNION ALL
SELECT d.Assetnum, d.Parentnum
, [Level] + 1
FROM dbo.Data1 d
INNER JOIN CTE cte ON cte.Assetnum = d.Parentnum
)
SELECT *
FROM CTE
ORDER BY [Level], Assetnum;
July 30, 2018 at 7:37 am
vee van gelder - Monday, July 30, 2018 2:06 AMi have some data which has a variable amount of hiearchy levels to it, DAX has a function called Path(), which gets the full path of the item i.e Path(id, parentid), what is the best way to get the levels in SQL, the id's are text fields and its assetnum and parentum.I know you can do do self joins but that is such a convoluted and labourious way, does sql have any functions that can help do this more dynaically, like the DAX method?
Kill 32 birds with one stone. Please see the following articles...
http://www.sqlservercentral.com/articles/Hierarchy/94040/
http://www.sqlservercentral.com/articles/T-SQL/94570/
--Jeff Moden
Change is inevitable... Change for the better is not.
July 30, 2018 at 9:16 am
vee van gelder - Monday, July 30, 2018 2:06 AMi have some data which has a variable amount of hiearchy levels to it, DAX has a function called Path(), which gets the full path of the item i.e Path(id, parentid), what is the best way to get the levels in SQL, the id's are text fields and its assetnum and parentum.I know you can do do self joins but that is such a convoluted and labourious way, does sql have any functions that can help do this more dynaically, like the DAX method?
I've written a whole book on this topic! Get a copy of my trees and hierarchies in SQL. I strongly recommend that you look at the nested set model (just Google it. There's lots of articles on it), since levels are very easy to write a single query that can be put in a view. There's no need to use loops or recursion or any other procedural code this model.
Please post DDL and follow ANSI/ISO standards when asking for help.
July 30, 2018 at 1:05 pm
Drop Table If Exists #Data1
Go
Create Table #Data1
(
AssetNum Char(6) Not Null,
NodeID HierarchyID Not Null Primary Key,
ParentNodeID As NodeID.GetAncestor(1) Persisted
)
Go
Create Or Alter Procedure #AddChild(@AssetNum Char(6), @ParentAssetNum Char(6))
As
Begin
Declare @ParentNodeId HierarchyId = (Select NodeID From #Data1 Where AssetNum = @ParentAssetNum)
Insert Into #Data1
Values(
@AssetNum,
@ParentNodeId.GetDescendant((Select Max(NodeId) From #Data1 Where ParentNodeID = @ParentNodeId), Null))
End
Go
Insert Into #Data1 Values('1', HierarchyID::GetRoot())
Exec #AddChild '2','1'
Exec #AddChild '3','1'
Exec #AddChild '4','3'
Exec #AddChild '5','4'
Exec #AddChild '6','4'
Exec #AddChild '7','3'
Exec #AddChild '8','4'
Select
CHILD.AssetNum,
PARENT.AssetNum,
CHILD.NodeID.GetLevel() As [Level],
CHILD.NodeID.ToString() As [Path]
From #Data1 CHILD
Left Join #Data1 PARENT On PARENT.NodeID = CHILD.ParentNodeID
Order By [Level], CHILD.AssetNum
At some point I'll have to try going through Jeff's great articles and seeing whether HierarchyId performs better or not than the adjacency list/nested set models (unless someone already has?)
July 31, 2018 at 7:27 am
andycadley - Monday, July 30, 2018 1:05 PMAt some point I'll have to try going through Jeff's great articles and seeing whether HierarchyId performs better or not than the adjacency list/nested set models (unless someone already has?)
Here's a comparison of recursive CTE vs. Enumerated Path vs. Nested Set vs. HierarchyID vs. Kimball Hierarchy Bridge from 2011 by John Simon:
https://jsimonbi.wordpress.com/2011/03/01/sql-hierarchy-comparative-performance-2/
July 31, 2018 at 8:42 am
hi everyone, thanks so much for all your helpful responses, ive played around with a few of them. the biggest issue i am having is how that i have the levels, how do i go about displaying this long chain? probably not a sql question, ideally i would want to see from level 0 - level 8 expanded out in the data to see the chain, but can't figure out how to do it!
July 31, 2018 at 8:50 am
vee van gelder - Tuesday, July 31, 2018 8:42 AMhi everyone, thanks so much for all your helpful responses, ive played around with a few of them. the biggest issue i am having is how that i have the levels, how do i go about displaying this long chain? probably not a sql question, ideally i would want to see from level 0 - level 8 expanded out in the data to see the chain, but can't figure out how to do it!
You need to read the articles at the two links I posted. It takes care of all that. Otherwise, you're either going to be stuck with recursion or the ardors of using the HierarchyID. Spend the time up front to do things right and all these other things will become child's play.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 31, 2018 at 8:58 am
i did run the code for the first one and have the results in the table, i know the article is really useful but its very wordy so its hard to get to the crux of what i am looking for and it not very clear to me how to split out those levels to see the chain, using the results of the first script, i did quick skim through the table results, does not show me how the levels are displayed, probably need more time than i have to digest what you have done. Doing things properly in a POC phase is a luxury unfortunately so i know its down to me not having enough time to digest
thanks anyway
July 31, 2018 at 11:57 am
I've found that doing things right in the POC phase is critical rather than a luxury. 😀 If you don't get things right during the POC, you might miss out on a solution and suffer greatly in the future for it.
I agree that article is long. It's meant to teach rather than provide a black-box solution that people won't be able to support in the future.
You asked for the "best" way to get levels. Neither recursion nor DAX is the best way. If you were to pony up the test data you're using for your POC in the form identified by the article at the first link under "Helpful Links" in my signature line below, then I could easily demonstrate how to do this and a whole bunch more.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 31, 2018 at 3:21 pm
RAThor - Tuesday, July 31, 2018 7:27 AMandycadley - Monday, July 30, 2018 1:05 PMAt some point I'll have to try going through Jeff's great articles and seeing whether HierarchyId performs better or not than the adjacency list/nested set models (unless someone already has?)Here's a comparison of recursive CTE vs. Enumerated Path vs. Nested Set vs. HierarchyID vs. Kimball Hierarchy Bridge from 2011 by John Simon:
https://jsimonbi.wordpress.com/2011/03/01/sql-hierarchy-comparative-performance-2/
Thanks! I had actually never seen this article before. It also feels good to know that my advocacy of the nested sets model has some justification
Please post DDL and follow ANSI/ISO standards when asking for help.
August 1, 2018 at 1:27 am
you kinda of making assumptions about what is needed for the POC, thanks for help though i have enough of what i need.
August 1, 2018 at 6:59 am
vee van gelder - Wednesday, August 1, 2018 1:27 AMyou kinda of making assumptions about what is needed for the POC, thanks for help though i have enough of what i need.
That's not the nicest thing to say... After all, we are all volunteers, and we don't often get a choice about whether or not to make assumptions. Few of us like that very much, but when people post their questions and don't provide complete details, just as you did, it shouldn't be a big surprise... I'd sooner believe that you don't understand the nature of your own POC project than believe that any assumptions Jeff Moden made are either unreasonable or unrealistic. When you say something like you just posted, you're "kinda" acting like a spoiled brat that just knows it all, that thinks everyone else can just go take a hike. Can the attitude and you'll get a lot more help. Try to remember that we can't magically see into your project and know anything other than what you tell us in your posts.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply