Hierarchical Query

• Mike Scalise

  SSCrazy Eights

  Points: 8145

  More actions

  October 4, 2015 at 5:59 pm

  #321646

  Hi,

  I have a table that contains a list of reports along with the reports they fully encompass and the reports that they are encompassed by. For example, report "a" has first name, last name, and dob. Report "b" has just first name and last name. Therefore, report a encompasses report b (and anything that report b encompasses).

  Here's a sample table:

  CREATE TABLE [dbo].[reporttable](

  [report] [varchar](1) NULL,

  [encompasses] [varchar](100) NULL,

  [encompassedby] [varchar](100) NULL

  ) ON [PRIMARY]

  INSERT [dbo].[reporttable] ([report], [encompasses], [encompassedby]) VALUES (N'a', N'b,c,e,f,g,m,s', NULL)

  INSERT [dbo].[reporttable] ([report], [encompasses], [encompassedby]) VALUES (N'b', N'c,e,f,g', N'a')

  INSERT [dbo].[reporttable] ([report], [encompasses], [encompassedby]) VALUES (N'c', N'e', N'a,b')

  INSERT [dbo].[reporttable] ([report], [encompasses], [encompassedby]) VALUES (N'e', NULL, N'a,b,c')

  INSERT [dbo].[reporttable] ([report], [encompasses], [encompassedby]) VALUES (N'f', NULL, N'a,b')

  INSERT [dbo].[reporttable] ([report], [encompasses], [encompassedby]) VALUES (N'g', NULL, N'a,b')

  INSERT [dbo].[reporttable] ([report], [encompasses], [encompassedby]) VALUES (N'm', N's', N'a')

  INSERT [dbo].[reporttable] ([report], [encompasses], [encompassedby]) VALUES (N's', NULL, N'a,m')

  INSERT [dbo].[reporttable] ([report], [encompasses], [encompassedby]) VALUES (N'r', N't', NULL)

  INSERT [dbo].[reporttable] ([report], [encompasses], [encompassedby]) VALUES (N't', NULL, N'r')

  INSERT [dbo].[reporttable] ([report], [encompasses], [encompassedby]) VALUES (N'n', NULL, NULL)

  SELECT *

  FROM reporttable

  I'm trying to develop a query that rolls everything up to the highest possible level. In this case, it would look like this:

  CREATE TABLE [dbo].[reporttableresult](

  [report] [varchar](1) NULL,

  [encompasses] [varchar](100) NULL

  ) ON [PRIMARY]

  GO

  INSERT [dbo].[reporttableresult] ([report], [encompasses]) VALUES (N'a', N'b,m')

  INSERT [dbo].[reporttableresult] ([report], [encompasses]) VALUES (N'r', N't')

  INSERT [dbo].[reporttableresult] ([report], [encompasses]) VALUES (N'n', NULL)

  SELECT *

  FROM reporttableresult

  I've seen some similar examples using a person and a manager, joining a table to itself using a recursive CTE, etc. However, I'm not exactly sure what to do here because each report doesn't list just its child, it lists its grandchildren too, so it's a little different.

  Thoughts?

  Thanks in advance,

  Mike

  Mike Scalise, PMP
  https://www.michaelscalise.com