Display all the parents till the top most in comma separated string

Question

I have a SQL server table (we use SQL server 2017), Org_Relationship, which has just two columns OrgId and ParentOrgId. The ParentOrgId column holds the Parent org id value of any given org as long as it's available. I need to display the Hierarchy of all the orgs in a comma separated concatenated string starting with the immediate parent and going all the way to the ultimate parent.

There are many similar articles on stack overflow discussing similar topics but I was unable to find a solution that matches my needs. I could be overlooking solutions already provided for similar issues as I am new to SQL Server.

Given below is the create script and some sample data :

CREATE TABLE [dbo].[OrgHiearchy](
    [OrgId] [nvarchar](50) NOT NULL,
    [ParentOrgID] [nvarchar](50) NULL
)

The expected output for say org Ids 100 and 200 is:

Thank you very much in advance for all the help.

Answer 1

Recursive CTEs can be tricky, but that is what you want here:

with cte as (
      select oh.OrgId, oh.ParentOrgId,
             convert(varchar(max), oh.ParentOrgId) as parents,
             oh.ParentOrgId as working, 1 as lev
      from OrgHierarchy oh
      where not exists (select 1 from OrgHierarchy oh2 where oh2.ParentOrgId = oh.OrgId)
      union all
      select cte.OrgId, cte.ParentOrgId,
             concat(parents, ',', convert(varchar(max), oh.ParentOrgId)),
             oh.ParentOrgId, lev + 1
      from cte join
           OrgHierarchy oh
           on oh.OrgId = cte.working
      where lev < 5
      )
select top (1) with ties orgid, parentorgid, parents
from cte
order by row_number() over (partition by orgid order by lev desc);

Here is a db<>fiddle.

Answer 2

First, we will build the hierarchies. For this, we will make a recursive CTE called "Hierarchies".

The anchor-part of the CTE will select the "starting points" of the hierarchies. We can make the base query including this anchor query in the CTE like this:

WITH
    [Hierarchies] ([BaseId], [Level], [Id]) AS
    (
        SELECT [OrgId], 1, [ParentOrgId]
        FROM [OrgHierarchy]
    )
SELECT *
FROM [Hierarchies]
ORDER BY [BaseId], [Level];

which provides the following output:

BaseId      Level      Id
100         1          600011944
200         1          1045
1045        1          250013
250013      1          600021987
600011944   1          600011945

The BaseId field will always contain the Id of the starting point. For each new level in the hierarchy, that BaseId field will be duplicated. The Level field will increase for each step, of course. And the Id field will contain the parent id for that specific level.

Thus we can extend the CTE by adding a recursive query (which is separated from the anchor query using a UNION ALL operator) like this:

WITH
    [Hierarchies] ([BaseId], [Level], [Id]) AS
    (
        SELECT [OrgId], 1, [ParentOrgId]
        FROM [OrgHierarchy]
      UNION ALL
        SELECT [BaseId], [Level] + 1, [ParentOrgId]
        FROM [OrgHierarchy] INNER JOIN [Hierarchies] ON [Id] = [OrgId]
    )
SELECT *
FROM [Hierarchies]
ORDER BY [BaseId], [Level];

which provides the following output:

BaseId      Level      Id
100         1          600011944
100         2          600011945
200         1          1045
200         2          250013
200         3          600021987
1045        1          250013
1045        2          600021987
250013      1          600021987
600011944   1          600011945

This data is structurally fine. Now we will update the main query below the CTE to "group" the parent ids in a comma-separated value. We could use a FOR XML query for that purpose, but since we are already recursively walking through the hierarchies, we could also build the CSVs in the CTE.

Let's include an additional field in the CTE called "Hierarchy", being of a long varchar type that can hold all comma-separated parent ids. The anchor query will just put the first parent id in it. The recursive query will add a comma and the new parent id.

WITH
    [Hierarchies] ([BaseId], [Level], [Id], [Hierarchy]) AS
    (
        SELECT [OrgId], 1, [ParentOrgId], CAST([ParentOrgId] AS VARCHAR(MAX))
        FROM [OrgHierarchy]
      UNION ALL
        SELECT [BaseId], [Level] + 1, [ParentOrgId], [Hierarchy] + ', ' + CAST([ParentOrgId] AS VARCHAR(MAX))
        FROM [OrgHierarchy] INNER JOIN [Hierarchies] ON [Id] = [OrgId]
    )
SELECT *
FROM [Hierarchies]
ORDER BY [BaseId], [Level];

which provides the following output:

BaseId      Level      Id          Hierarchy
100         1          600011944   600011944
100         2          600011945   600011944, 600011945
200         1          1045        1045
200         2          250013      1045, 250013
200         3          600021987   1045, 250013, 600021987
1045        1          250013      250013
1045        2          600021987   250013, 600021987
250013      1          600021987   600021987
600011944   1          600011945   600011945

Looks like we're almost there now. We just need to select all final records. For that, we use a CROSS apply in the main query that combines the original fields in the OrgHierarchy table with the last corresponding CTE records.

WITH
    [Hierarchies] ([BaseId], [Level], [Id], [Hierarchy]) AS
    (
        SELECT [OrgId], 1, [ParentOrgId], CAST([ParentOrgId] AS VARCHAR(MAX))
        FROM [OrgHierarchy]
      UNION ALL
        SELECT [BaseId], [Level] + 1, [ParentOrgId], [Hierarchy] + ', ' + CAST([ParentOrgId] AS VARCHAR(MAX))
        FROM [OrgHierarchy] INNER JOIN [Hierarchies] ON [Id] = [OrgId]
    )
SELECT [OrgId], [ParentOrgId], [Hierarchy]
FROM
    [OrgHierarchy]
    CROSS APPLY (SELECT TOP (1) [Hierarchy]
                 FROM [Hierarchies]
                 WHERE [BaseId] = [OrgId]
                 ORDER BY [Level] DESC) AS H

which provides the following output:

OrgId      ParentOrgId      Hierarchy
100        600011944        600011944, 600011945
200        1045             1045, 250013, 600021987
1045       250013           250013, 600021987
250013     600021987        600021987
600011944  600011945        600011945

Looks like we're done now. :)

Answer 3

The Postgres OnLine Journal has a solution to your need(The article link). I took courage to adapt it to MS SQL Server.

CREATE TABLE supplyitem(si_id integer PRIMARY KEY, si_parentid integer, si_item varchar(100));

INSERT INTO supplyitem(si_id,si_parentid, si_item)
VALUES (1, NULL, 'Paper'),
(2,1, 'Recycled'),
(3,2, '20 lb'),
(4,2, '40 lb'),
(5,1, 'Non-Recycled'),
(6,5, '20 lb'),
(7,5, '40 lb'),
(8,5, 'Scraps');

WITH supplytree AS
(SELECT si_id, si_item, si_parentid, CAST(si_item As varchar(1000)) As si_item_fullname
FROM supplyitem
WHERE si_parentid IS NULL
UNION ALL
SELECT si.si_id,si.si_item,
    si.si_parentid,
    CAST(sp.si_item_fullname + '->' + si.si_item As varchar(1000)) As si_item_fullname
FROM supplyitem As si
    INNER JOIN supplytree AS sp
        ON (si.si_parentid = sp.si_id)
)
SELECT si_id, si_item_fullname
FROM supplytree
ORDER BY si_item_fullname;

The output:

si_id   si_item_fullname
1   Paper
5   Paper->Non-Recycled
6   Paper->Non-Recycled->20 lb
7   Paper->Non-Recycled->40 lb
8   Paper->Non-Recycled->Scraps
2   Paper->Recycled
3   Paper->Recycled->20 lb
4   Paper->Recycled->40 lb

SQL Fiddle