Generate Parent-Child Relation in SQL Server and PostgreSQL

This article gives an explanation about how to create parent-child relation by splitting the string in SQL server as well as in PostgreSQL and also shows you how you can manage hierarchical relational rows in SQL server as well as in PostgreSQL and how to create a hierarchical folder structure from any file path or string by splitting the string from a specific character such as split comma separated string or split string by '/' or split string by '»' or split the string from any of character as well as show you how to create a query to generate folder structure in SQL server and also in the PostgreSQL.

In my previous articles, I Explained How to Split Comma Separated String in SQL Server as well as the query to return Date, Day, Month, Weekday, Quarter, Day of Year, Week of Year, Month Name, and Weekday Name in SQL Server and Cursor in SQL Server With Syntax and Example as well as How to create comma separated string from rows using STUFF() Function in SQL Server With Syntax and Example and in this article I explained how to create folder structure with parent-child relation by splitting the input string in SQL server as well as in PostgreSQL.

Yesterday I got a new task/requirement from my team to write a Query in SQL server and PostgreSQL to create/generate folder structure with the parent-child relationship in the tabular format. They give a file path as an input string and an expected output sample where I am required to split the string by specific character and generate the table with parent-child relation as per the given expected output shown below in the requirement section.

Requirement

Expected Input

'ABC»DEF»GHI»JKL»MNO»PQR»STU»VWX»YZ'

Expected Output

Implementation

So, let us start to implement a query to split comma-separated strings and generate folder structure from the given file path with parent-child relation.

SQL Server

DECLARE  @WorkTbl AS TABLE
(Id INT, Folder VARCHAR(50))
 
INSERT INTO @WorkTbl VALUES ('1','ABC » DEF » GHI » JKL » MNO » PQR » STU» VWX » YZ')
 
SELECT Child.Id, LTRIM(RTRIM(Child.Child)) As Child, LTRIM(RTRIM(Parent.Parent)) As Parent 
FROM (
        SELECT *, CASE WHEN ChildId=1 THEN NULL ELSE ChildId-1 END As ParentId  
  FROM (
                SELECT Id, ROW_NUMBER() OVER (ORDER BY Id) As ChildId, T.ItemValue As Child
                FROM @WorkTbl
                CROSS APPLY (SELECT * FROM Split(Folder,'»')) T
                ) AS C
        ) AS Child
LEFT JOIN ( SELECT Id, ROW_NUMBER() OVER (ORDER BY Id) As ParentId, T.ItemValue As Parent
                FROM @WorkTbl
                CROSS APPLY (SELECT * FROM Split(Folder,'»')) T
                ) Parent ON Child.ParentId = Parent.ParentId
ORDER BY Child.ChildId

PostgreSQL (pgsql)

CREATE TEMP TABLE WorkTbl (Id int,Folder varchar(50))
INSERT INTO WorkTbl values(1,'ABC»DEF»GHI»JKL»MNO»PQR»STU»VWX»YZ')
 
 
SELECT C.Id, D.splitfields AS Child, C.splitfields As Parent  
 FROM(
  SELECT RNO, splitfields,CASE WHEN RNO=1 THEN NULL ELSE RNO - 1  END AS RNO2  
  FROM (                                                                                                                  
    select Id,
    ROW_NUMBER()OVER( ORDER BY Id) AS RNO,                                                                                                               
    splitfields
    from (
      select t.Id, Folder, x.splitfields
      from WorkTbl t
      cross join unnest(string_to_array(t.Folder, '»')) as x(splitfields)
       ) x
   )B
  )C
LEFT JOIN ( SELECT RNO, splitfields,CASE WHEN RNO=1 THEN NULL ELSE RNO - 1  END AS RNO2 
 FROM (                                                                                                                      
   select Id,
   ROW_NUMBER()OVER( ORDER BY Id) AS RNO,                                                                                                               
   splitfields
   from (
     select t.Id, Folder, x.splitfields
     from WorkTbl t
     cross join unnest(string_to_array(t.Folder, '»')) as x(splitfields)
      ) x
   )B
  ) D ON C.RNO2 =D.RNO
ORDER BY C.RNO;

Explanation

SQL Server

As you can see in the above query of SQL Server and PostgreSQL first I split the input string with the '»' character and to split the input string in SQL Server I have created Split Function to split the string with any specific character that I already explained in my previous article how you can split comma separated string in SQL server, so if you do not have this function then you should create this function and using this function you can split the string as I showed in the Query of the SQL Server.

Then I selected the required columns Id and generated row number order by "Id" and then split the column of "Folder" from my table and return a table with alias "T" and cross apply table "T" with my source table "@WorkTbl"  and put this query as subquery with alias "C". Then again select columns "Id", RowNumber as "child" and separated value from table "T" as ItemValue and add CASE WHEN Statment like when RowNumber as "ChildId" = 1 Then NULL else RowNumber as "child" - 1 as ParentId and put this query again as a subquery and give table alias as Child.

Now, Put the same created subquery as in join and join as with alias Parent and apply left join on Child.ParentId = Parent.ParentId (here in the query of the parent "child acts as "ParentId" as shown in the query). Finally, set its order ORDER BY Child. child.

PostgreSQL (pgsql)

As I explained above query for SQL Server here logic remains the same and everything is based on RowNumber and Joins Here in PostgreSQL I have just used some different functions to split the string because PostgreSQL cannot support the same syntax and function as we created in SQL Server, So, here I have used some of the inbuilt function provided by PostgreSQL such as unnest and string_to_array parts else everything is same as I explained for SQL Server Query. Still, If you have any questions or doubts then leave your comment I will help you to prepare your query for the same.

Output 

Summary

In this article, we learned how to create a parent-child relationship between different kinds of records as well as how you can manage hierarchical folder structure in SQL Server as well as in PostgreSQL, and how to split the string with any specific character and return table in SQL Server and in PostgreSQL as well as also learned a different kind of joins, subqueries, and some other logic.