Generate Parent-Child Relation in SQL Server and PostgreSQL

watch_later 1/17/2023

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

  • Write a Query to Generate Parent-Child Relation Rows By Splitting String in SQL Server and PostgreSQL and Return Table.
  • For the First Record, the Parent should be null or empty.
  • The Parent of the Second Record is the First Record and the Parent of The Third Record is Second Record and So On... as per shown in the expected output.

Expected Input

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

Expected Output


Query to Generate Parent-Child Relation Rows By Splitting String in SQL Server and PostgreSQL

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=THEN NULL ELSE ChildId-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=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=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 

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.

Codingvila provides articles and blogs on web and software development for beginners as well as free Academic projects for final year students in Asp.Net, MVC, C#, Vb.Net, SQL Server, Angular Js, Android, PHP, Java, Python, Desktop Software Application and etc.

Thank you for your valuable time, to read this article, If you like this article, please share this article and post your valuable comments.

Once, you post your comment, we will review your posted comment and publish it. It may take a time around 24 business working hours.

Sometimes I not able to give detailed level explanation for your questions or comments, if you want detailed explanation, your can mansion your contact email id along with your question or you can do select given checkbox "Notify me" the time of write comment. So we can drop mail to you.

If you have any questions regarding this article/blog you can contact us on info.codingvila@gmail.com

sentiment_satisfied Emoticon