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

watch_later Friday, March 22, 2019

Introduction


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 show 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 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, 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 expected output sample where I required to split the string by specific character and generate the table with parent-child relation as per given expected output as I Shown below in requirement section.

Requirement


1) Write a Query to Generate Parent-Child Relation Rows By Splitting String in SQL Server and PostgreSQL and Return Table.
2) For First Record Parent should be null or empty.
3) The Parent of the Second Record is 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, lets we start to implement query to split comma separated string 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 have split the input string with '»' 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 have Select required columns Id and generate 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 sub query with alias "C". Then again select columns "Id", RowNumber as "ChildId" and separated value from table "T" as ItemValue and add CASE WHEN Statment like when RowNumber as "ChildId" = 1 Then NULL else RowNumber as "ChildId" - 1 as ParentId and put this query again as subquery and give table alias as Child.

Now, Put 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 "ChildId act as "ParentId" as shown in the query). Finally, set its order ORDER BY Child.ChildId.

PostgreSQL (pgsql)


As I Explained above query for SQL Server here logic remain 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 functions else everything is same as I explained for SQL Server Query. Still, If you have any questions or doubts then you just leave your comment I will help you to prepare your query for same.

Output 

Output

Summary


This article we learned how to create a parent-child relationship between different kind 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 different kind of joins, subqueries and some other logic also.

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.



sentiment_satisfied Emoticon