TechSapphire Friday, 2018-09-21, 8:23 PM
Site menu
Login form
News
Play Games
  • Deep Freeze
  • Ice Slide
  • Gyroball
  • Fat Fish
  • Bush Royal Rampage
  • Grandparent, parent and child hierarchy SQL server or Bread Crumb


    1. Getting parent child relation using self join
    2. Small introduction to CTE
    (Recursive CTE)
    3. Ordering records according to hierarchy
    4. Keeping root parent with each record

    Table script:

    /****** Object:  Table [dbo].[HierarchyTB]    Script Date: 11/01/2015 13:08:28 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[HierarchyTB](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [EmpID] [int] NULL,
        [ParentID] [int] NULL,
        [Description] [varchar](50) NULL,
     CONSTRAINT [PK_HierarchyTB] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    SET IDENTITY_INSERT [dbo].[HierarchyTB] ON
    INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (1, 1, 0, N'Main Parent Yogi')
    INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (2, 2, 0, N'Main Parent Ron')
    INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (3, 3, 0, N'Main Parent Don')
    INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (4, 4, 2, N'Child Jaz')
    INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (5, 5, 2, N'Child Rat')
    INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (6, 6, 1, N'Child Tom')
    INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (7, 7, 2, N'Child Jerry')
    INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (8, 8, 3, N'Child Din')
    INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (9, 9, 1, N'Child Minny')
    INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (10, 10, 3, N'Child Micky')
    INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (11, 11, 3, N'Child Goofy')
    INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (12, 12, 3, N'Child Daisy')
    INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (13, 13, 6, N'Sub Child Popey')
    INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (14, 14, 6, N'Sub Child Zen')
    INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (15, 15, 9, N'Sub Child Shin Chan')
    INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (16, 16, 10, N'Sub Child Doremon')
    INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (17, 17, 9, N'Sub Child Pikachu')
    INSERT [dbo].[HierarchyTB] ([Id], [EmpID], [ParentID], [Description]) VALUES (18, 18, 8, N'Sub Child Tweety')
    SET IDENTITY_INSERT [dbo].[HierarchyTB] OFF

     

    Script for Query:

    --Data Output
    SELECT *
    FROM HierarchyTB
    --Using full qualified column names
    SELECT HierarchyTB.EmpID,
           HierarchyTB.ParentID
    FROM HierarchyTB
    --Using Alias
    SELECT H1.EmpID,
           H1.ParentID
    FROM HierarchyTB AS H1
    --Getting Parent and child name using SELF JOIN
    SELECT H1.EmpID,
           H1.ParentID,
           isnull(h2.Description,'Root') [Parent Description],
           h1.Description [Self Description]
    FROM HierarchyTB H1
    LEFT JOIN HierarchyTB H2 ON H1.ParentID=h2.EmpID
    --Introduction to CTE
    WITH OutputCTE(EmployeeID,ParentID,ParentDescription,SelfDescription)
    AS
    (
    SELECT H1.EmpID,
           H1.ParentID,
           isnull(h2.Description,'Root') [Parent Description],
           h1.Description [Self]
    FROM HierarchyTB H1
    LEFT JOIN HierarchyTB H2 ON H1.ParentID=h2.EmpID
    )
    SELECT * FROM OutputCTE;
    --Getting output in hierarchical order
     WITH CTE AS
      (SELECT H1.EmpID,
              H1.ParentID,
              h1.Description [Parent],
              Description [Self Description],
              CAST(id AS varbinary(MAX)) [Level]
       FROM HierarchyTB H1
       WHERE h1.ParentID=0
       UNION ALL 
       SELECT H2.EmpID,
                        H2.ParentID,
                        c.[Self Description],
                        Description [Self Description],
                        c.[Level]+CAST(h2.id AS varbinary(MAX)) AS [Level]
       FROM HierarchyTB H2
       INNER JOIN CTE c ON h2.ParentID=c.EmpID)
    SELECT *
    FROM CTE
    ORDER BY [Level]
     OPTION (MAXRECURSION 1000) 
    ;
    --Getting hierarchical order with Root on each level
    WITH CTE AS
      (SELECT H1.EmpID,
              H1.ParentID,
              h1.Description [Parent],
              Description [Self Description],
              CAST(id AS varbinary(MAX)) [Level],
              CAST (h1.id AS varchar(max)) [LevelID]
       FROM HierarchyTB H1
       WHERE h1.ParentID=0
       UNION ALL SELECT H2.EmpID,
                        H2.ParentID,
                        c.[Self Description],
                        Description [Self Description],
                        c.[Level]+CAST(h2.id AS varbinary(MAX)) AS [Level],
                                      c.[LevelID] + '>' + CAST (h2.id AS varchar(max)) [LevelID]
       FROM HierarchyTB H2
       INNER JOIN CTE c ON h2.ParentID=c.EmpID)
    SELECT *
    FROM CTE CROSS apply
      ( SELECT SUBSTRING(LevelID,1,CHARINDEX('>',LevelID+ '>')-1) ) c(RootLevelID)
    ORDER BY [Level] OPTION (MAXRECURSION 1000)

     

    Categories
    Programming [27]
    Tips for programming
    Security [2]
    Security Tips
    Google [1]
    Use google faster then ever you use
    Project [14]
    HTML [2]
    Electronics [0]
    Data Structure [0]
    Database [16]
    SQL SERVER
    SSRS [1]
    Sql Server Reporting Services
    Copyright MyCorp © 2018