How to write T-SQL Recursive Query to return hierarchical data using common self-joining table to store tree data

Question

Every time I try to revisit recursive queries I feel like I am starting over. I am wanting to query data from a table that stores hierarchical data using a common method, having a table that self-joins.

First of all, there is this table that stores "Groups", i think of as being "folders" using a Windows Explorer analogy. The ID is the PK and there is an associated group Name.

CREATE TABLE [dbo].[BPAGroup](
    [id] [varchar](10) NOT NULL,
    [name] [nvarchar](255) NOT NULL,
 CONSTRAINT [PK_BPAGroup] 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

Second and lastly, there is a relationship table that associates a group with its parent group. "GroupID" identifies "MemberID"'s group. For example, if you join up BPAGroupGroup.MemberID to [BPAGroup].ID, [BPAGroup].Name would contain the name of the group. if you join up BPAGroupGroup.GroupID to [BPAGroup].ID, [BPAGroup].Name would contain the name of the PARENT group.

CREATE TABLE [dbo].[BPAGroupGroup](
    [memberid] [varchar](10) NOT NULL,
    [groupid] [varchar](10) NOT NULL,
 CONSTRAINT [PK_BPAGroupGroup] PRIMARY KEY CLUSTERED 
(
    [memberid] ASC,
    [groupid] 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
-------------------------
ALTER TABLE [dbo].[BPAGroupGroup]  WITH CHECK ADD  CONSTRAINT [FK_BPAGroupGroup_BPAGroup_groupid] FOREIGN KEY([groupid])
REFERENCES [dbo].[BPAGroup] ([id])
GO

ALTER TABLE [dbo].[BPAGroupGroup] CHECK CONSTRAINT [FK_BPAGroupGroup_BPAGroup_groupid]
GO

ALTER TABLE [dbo].[BPAGroupGroup]  WITH CHECK ADD  CONSTRAINT [FK_BPAGroupGroup_BPAGroup_memberid] FOREIGN KEY([memberid])
REFERENCES [dbo].[BPAGroup] ([id])
GO

ALTER TABLE [dbo].[BPAGroupGroup] CHECK CONSTRAINT [FK_BPAGroupGroup_BPAGroup_memberid]
GO

Here's some sample data in the form

Level1
   Level2
       Level3

and the SQL for the data

INSERT [dbo].[BPAGroup] ([id], [name]) VALUES (N'A', N'Level1')
INSERT [dbo].[BPAGroup] ([id], [name]) VALUES (N'B', N'Level2')
INSERT [dbo].[BPAGroup] ([id], [name]) VALUES (N'C', N'Level3')
INSERT [dbo].[BPAGroupGroup] ([memberid], [groupid]) VALUES (N'B', N'A')
INSERT [dbo].[BPAGroupGroup] ([memberid], [groupid]) VALUES (N'C', N'B')

How can I write a recursive T-SQL Server Query that returns all of the groups names, the recursion "level number" and IDs for all of the groups. Of course, the root of the tree would have a NULL ParentID and ParentName?

For example, these fields would be in the result set.

Level, GroupID, GroupName, ParentId, ParentName

I realize that there are multiple ways to store this type of data. I don't have flexibility to change the db design.

Ideally, the result should show all group names, even the root node, that doesn't have a parent.

Could you post some sample data and expected results please? I suspect I know the answer, however, I'd have to make some guesses on your data. — Larnu
Thank you. Sure, I'll make the GUID field a varchar and come up with all the SQL for the schema and data that you need.Working on it now.. — Ivan

Larnu Larnu · Accepted Answer · 2019-02-23T19:48:10

Based on the latest data, this appears to get you the result you want:

WITH rCTE AS(
    SELECT 1 AS Level,
           id AS GroupID,
           [name] AS GroupName,
           CONVERT(nvarchar(10),NULL) AS ParentID, --This'll be uniqueidentifier in your real version
           CONVERT(nvarchar(255),NULL) AS ParentName
    FROM BPAGroup G
    WHERE NOT EXISTS (SELECT 1
                      FROM BPAGroupGroup e
                      WHERE e.memberid = G.id)
    UNION ALL
    SELECT r.Level + 1,
           G.id AS GroupID,
           G.[name] AS GroupName,
           r.GroupID AS ParentID,
           r.[GroupName] AS ParentName
    FROM BPAGroup G
         JOIN BPAGroupGroup GG ON G.id = GG.memberid
         JOIN rCTE r ON GG.groupid = r.GroupID)
SELECT *
FROM rCTE;

db<>fiddle

It's important you understand how this works though. As you said in your post, you seem to need to revisit these each time. There's nothing wrong with needing to check the syntax for something (there's some things I fail miserably at remembering sometimes, especially the new OPENJSON stuff), but do you understand how this works? If not, which bit don't you?

How to write T-SQL Recursive Query to return hierarchical data using common self-joining table to store tree data

1 Answers