Sql Server Hierarchy With Parent Id And Child Id
Solution 1:
As per your request. Here is the code to build the full hierarchy. This results were constructed from the 85 rows of hierarchy that you supplied in your question.
I still don't know what you mean by Lowest Level. There are many lowest levels. I added a field IsParent which indicates whether or not a point is a parent/roll-up... Zero indicates a leaf or bottom node.
;With cteOH (id,ParentID,Lvl,CID,SortSeq)
as (
Select id,ParentID,Lvl=1,CID,SortSeq =cast(concat(id,'>') asvarchar(500)) from CO1 where ParentID=1105UnionAllSelect h.id,h.ParentID,cteOH.Lvl+1,h.CID ,SortSeq =cast(concat(cteOH.SortSeq,h.id)+'>'asvarchar(500)) FROM CO1 h INNERJOIN cteOH ON h.ParentID = cteOH.id
),
cteR1 as (Select id,SortSeq,r1=Row_Number() over (Orderby SortSeq) From cteOH),
cteR2 as (Select A.id,r2 =max(B.r1) From cteOH A Join cteR1 B on (B.SortSeq Like A.SortSeq+'%') GroupBy A.id)
Select B.R1
,C.R2
,A.Lvl
,A.ID
,A.ParentID
,A.CID
,IsParent = iif(R1<>R2,1,0)
Into dbo.CO1_Hier -- << Storing Results of Hier For Ease of UseFrom cteOH A
Join cteR1 B on (A.ID=B.ID)
Join cteR2 C on (A.ID=C.ID)
Results of Hierarchy Build
R1 R2 Lvl ID ParentID CID IsParent
-------------------- -------------------- ----------- ----------- ----------- -------------------------------------- -----------1851480391105All Service Groups1231213368648039 Network Services 13183133647133686 Network Voice Services 1454133649133647 Total VoiP 155548053133649 K909_GB05 0674133651133647Local Services Telco 177548054133651 K909_GB06 0894133654133647 Long Distance Telco 199548055133654 K909_GB07 010124133657133647 Telecom Project Management 111115133656133657 K909_GB08 01212548049133657 K909_GB01 013154133660133647 Telecom Wiring 114145133659133660 K909_GB09 01515548050133660 K909_GB02 016184133663133647 Shared CENTREX 11717548051133663 K909_GB03 01818548052133663 K909_GB10 01931348106133686 Network Data Services 12022413366748106 Total DTO Internet 12121548056133667 K909_GD01 02222548057133667 K909_GD07 02324413367048106 MetroNet 12424548058133670 K909_GD02 02526413367348106 Router Management 12626548060133673 K909_GD03 02729413367648106 Firll / VN Management 12828548061133676 K909_GD04 02929548062133676 K909_GD06 0303144810348106 Total WAN 1313154806648103 K909_GD05 03257213368948039 Shared Services 133453133634133689 Server Hosting Services 134344133638133634 K909_GG48 035354133679133634 K909_GG49 036364133680133634 K909_GG07 037374138164133634 K909_GG04 03838448076133634 K909_GG40 03939448077133634 K909_GG41 04040448078133634 K909_GG42 04141448079133634 K909_GG43 04242448080133634 K909_GG44 04343448081133634 K909_GG45 04444448082133634 K909_GG46 04545448083133634 K909_GG47 046513133640133689 Desktop Support & Application Services 147484133642133640 Application Services 14848548074133642 K909_GG30 049514133644133640 Total Desktop Support 150505133678133644 K909_GG21 05151548072133644 K909_GG20 05257348105133689 Mainframe Services 1535344806748105 K909_GG11 0545444806848105 K909_GG12 0555544806948105 K909_GG13 0565644807048105 K909_GG14 0575744807148105 K909_GG10 05862213369248039 SW Security K-12 Monitoring 159623133682133692 K12 Monitoring 160604133681133682 K909_GM00 061614133683133682 K909_GM01 062624133684133682 K909XGM01 06366213369548039 Print Services Group164643140641133695 K909_GZ00 06566348108133695 Total Print Services 1666644809548108 K909_GK00 06773213817048039 Miscellaneous Service Groups168683138162138170 K909XGA01 069693138163138170 Surplus 070703138165138170 Help Desk 071713138166138170 DS 072723138167138170 SCS 073733138168138170 IT Plan and Admin 0747824810048039 Ovhd Service Groups17575314063148100 K909_GG00 07676314063448100 K909_GA02 07777314065648100 K909_GB00 0787834804548100 G0000002 0798524810948039 Total Shared Services Security 1808034808948109 GH010001 0818134809048109 GH010002 0828234809148109 GH010003 0838334809248109 GH010004 0848434809348109 GH010005 0858534809448109 GH010006 0
(85row(s) affected)][1]][1]
Now, this is the cool part!
** I created some random data at the NON-PARENT or leaf level in table Co1_Trans
The following query illustrates how to roll the data up via the range keys
Select H.R1
,H.R2
,H.Lvl
,H.ID
,H.ParentID
,H.CID
,H.IsParent
,NestedDesc = space((H.Lvl-1)*2)+H.CID
,Records =count(*)
,MinVal =min(SomeValue)
,MaxVal =max(SomeValue)
,AvgVal =Avg(SomeValue)
,SomeValue =sum(SomeValue)
,SomeOtherValue =sum(SomeOtherValue)
From dbo.CO1_Hier H
Join (Select _R1=B.R1,A.*From [dbo].[CO1_Trans] A Join dbo.CO1_Hier B on (A.ID=B.ID)) B
on (B._R1 between H.R1 and H.R2)
GroupBy H.R1
,H.R2
,H.Lvl
,H.ID
,H.ParentID
,H.CID
,H.IsParent
OrderBy H.R1
The results are displayed here
Now, let's say that you only wanted to show Levels 1 and 2. Let's use the same query as before, but add a where clause .. Where H.Lvl<3
...
,SomeOtherValue = sum(SomeOtherValue)
From dbo.CO1_Hier H
Join (Select _R1=B.R1,A.* From [dbo].[CO1_Trans] A Join dbo.CO1_Hier B on (A.ID=B.ID)) B on (B._R1 between H.R1 and H.R2)
Where H.Lvl<3GroupBy H.R1
,H.R2
,H.Lvl
...
Solution 2:
If you know the lowest descendant at the beginning of your query then walk up through a recursive cte which would make this easier and more fool proof. If you don't know the lowest level then you need to walk down the tree to find it. So you have to know/be able to figure out either the top parent or the bottom descendant.
This answer works but only because there is only 1 descendant at the lowest level which seems very unlikely to me! But that is the issue, how would you choose which heirarchyid to put at the parent when there is more than 1 descendant at the lowest level? Or would you want the parent etc. to be duplicated which coincidentally this query will do in that case.
;WITH cteRecursive AS (
SELECT
GreatestAncestorId = Id
,Id
,ParentId
,Level =0FROM@Table t
WHERE
ParentId =1105UNIONALLSELECT
c.GreatestAncestorId
,t.Id
,t.ParentId
,Level = c.Level +1FROM@Table t
INNERJOIN cteRecursive c
ON t.ParentId = c.Id
)
, cteLowestDecendent AS (
SELECT TOP 1 Id AS LowestDecendatId, GreatestAncestorId
FROM
cteRecursive
ORDERBY
Level DESC
)
SELECT`enter code here`
l.LowestDecendatId
,r.*FROM
cteRecursive r
INNERJOIN cteLowestDecendent l
ON r.GreatestAncestorId = l.GreatestAncestorId
There are a couple of interesting techniques on this post: SQL Hierarchy - Resolve full path for all ancestors of a given node check them out for more detail on walking up and down the tree or creating a different type of heirarchyid all together.
Solution 3:
I tend to use range keys on my hierarchies. With the range keys (R1 & R2), you can select and/or aggregate data without the need for recursive queries. Also, you know all ancestors and/or descendants. Now, I normally store my hierarchies with the ranges keys and rebuild as necessary.
This technique supports variable depth (or jagged) hierarchies with ease.
In the following example, normally the top node(s) have a parent of null, but the sample data did not, so in the anchor query I have where ParentID=1105
Declare@Tabletable (id int,ParentID int,Name varchar(50))
InsertInto@Table (ID,ParentID,Name) values
(48039,1105,'All Services'),
(133686,48039,'Ntw Services'),
(133647,133686,'Ntwk voice Services'),
(133649,133647,'Ntwk VOIP'),
(48053,133649,'LL800_GB05'),
(999999,133649,'Dummy to Illustrate multiple children')
Declare@GetIDint=48053-- Any Desired ID
;With cteOH (id,ParentID,Lvl,Name,SortSeq)
as (
Select id,ParentID,Lvl=1,Name,SortSeq =cast(concat(id,'>') asvarchar(500)) from@Tablewhere ParentID=1105UnionAllSelect h.id,h.ParentID,cteOH.Lvl+1,h.Name ,SortSeq =cast(concat(cteOH.SortSeq,h.id)+'>'asvarchar(500)) FROM@Table h INNERJOIN cteOH ON h.ParentID = cteOH.id
),
cteR1 as (Select id,SortSeq,r1=Row_Number() over (Orderby SortSeq) From cteOH),
cteR2 as (Select A.id,r2 =max(B.r1) From cteOH A Join cteR1 B on (B.SortSeq Like A.SortSeq+'%') GroupBy A.id)
Select HierarchyID = F.ID -- Added to to fullfill request
,B.R1 -- Included to Illustrate Range Keys
,C.R2 -- Included to Illustrate Range Keys
,A.Lvl -- Included to Illustrate Level of Node
,A.ID
,A.ParentID
,A.Name
From cteOH A
Join cteR1 B on (A.ID=B.ID)
Join cteR2 C on (A.ID=C.ID)
Join cteR1 F on (F.ID=@GetIDand F.R1 between B.R1 and C.R2)
Returns
HierarchyID R1 R2 Lvl ID ParentID Name
48053161480391105All Services
4805326213368648039 Ntw Services
48053363133647133686 Ntwk voice Services
48053464133649133647 Ntwk VOIP
4805355548053133649 LL800_GB05
Notice my dummy ID is not displayed
Now in this sample, I included Join cteR1 F so you can go Back UP the hierarchy
@Matt pointed out that I misunderstood the request, so I wanted a chance to redeem myself and introduce the the Range Key technique.
Solution 4:
A simplified Roll-To-Top
Declare@Tabletable (id int,ParentID int,Name varchar(50))
InsertInto@Table (ID,ParentID,Name) values
(48039,1105,'All Services'),
(133686,48039,'Ntw Services'),
(133647,133686,'Ntwk voice Services'),
(133649,133647,'Ntwk VOIP'),
(48053,133649,'LL800_GB05')
Declare@GetIDint=48053-- Any Desired ID
;With cteOH (Lvl,HierarchyId,id,ParentID,Name)
as (
Select Lvl=0,HierarchyId=@GetID,id,ParentID,Name from@Tablewhere ID=@GetIDUnionAllSelect cteOH.Lvl-1,HierarchyId=@GetID,h.id,h.ParentID,h.Name FROM@Table h INNERJOIN cteOH ON cteOH.ParentID = H.id
)
Select HierarchyId,id,ParentID,Name from cteOH Orderby Lvl
Returns
HierarchyId id ParentID Name
48053 48039 1105 All Services
48053 133686 48039 Ntw Services
48053 133647 133686 Ntwk voice Services
48053 133649 133647 Ntwk VOIP
48053 48053 133649 LL800_GB05
When I ran the FULL Hier Build on your data supplied, the results were immediate and as follows:
R1 R2 Lvl ID ParentID CID
-------------------- -------------------- ----------- ----------- ----------- --------------------------------------1851480391105All Service Groups231213368648039 Network Services
3183133647133686 Network Voice Services
454133649133647 Total VoiP
55548053133649 K909_GB05
674133651133647Local Services Telco
77548054133651 K909_GB06
894133654133647 Long Distance Telco
99548055133654 K909_GB07
10124133657133647 Telecom Project Management
11115133656133657 K909_GB08
1212548049133657 K909_GB01
13154133660133647 Telecom Wiring
14145133659133660 K909_GB09
1515548050133660 K909_GB02
16184133663133647 Shared CENTREX
1717548051133663 K909_GB03
1818548052133663 K909_GB10
1931348106133686 Network Data Services
2022413366748106 Total DTO Internet
2121548056133667 K909_GD01
2222548057133667 K909_GD07
2324413367048106 MetroNet
2424548058133670 K909_GD02
2526413367348106 Router Management
2626548060133673 K909_GD03
2729413367648106 Firll / VN Management
2828548061133676 K909_GD04
2929548062133676 K909_GD06
303144810348106 Total WAN
313154806648103 K909_GD05
3257213368948039 Shared Services
33453133634133689 Server Hosting Services
34344133638133634 K909_GG48
35354133679133634 K909_GG49
36364133680133634 K909_GG07
37374138164133634 K909_GG04
3838448076133634 K909_GG40
3939448077133634 K909_GG41
4040448078133634 K909_GG42
4141448079133634 K909_GG43
4242448080133634 K909_GG44
4343448081133634 K909_GG45
4444448082133634 K909_GG46
4545448083133634 K909_GG47
46513133640133689 Desktop Support & Application Services
47484133642133640 Application Services
4848548074133642 K909_GG30
49514133644133640 Total Desktop Support
50505133678133644 K909_GG21
5151548072133644 K909_GG20
5257348105133689 Mainframe Services
535344806748105 K909_GG11
545444806848105 K909_GG12
555544806948105 K909_GG13
565644807048105 K909_GG14
575744807148105 K909_GG10
5862213369248039 SW Security K-12 Monitoring
59623133682133692 K12 Monitoring
60604133681133682 K909_GM00
61614133683133682 K909_GM01
62624133684133682 K909XGM01
6366213369548039 Print Services Group64643140641133695 K909_GZ00
6566348108133695 Total Print Services
666644809548108 K909_GK00
6773213817048039 Miscellaneous Service Groups68683138162138170 K909XGA01
69693138163138170 Surplus
70703138165138170 Help Desk
71713138166138170 DS
72723138167138170 SCS
73733138168138170 IT Plan and Admin
747824810048039 Ovhd Service Groups7575314063148100 K909_GG00
7676314063448100 K909_GA02
7777314065648100 K909_GB00
787834804548100 G0000002
798524810948039 Total Shared Services Security
808034808948109 GH010001
818134809048109 GH010002
828234809148109 GH010003
838334809248109 GH010004
848434809348109 GH010005
858534809448109 GH010006
(85row(s) affected)
Post a Comment for "Sql Server Hierarchy With Parent Id And Child Id"