convert hierarchy structure to parent children pair please... 1

[peac3]

Hello SQL legends,

I have a table with a family structure vertical as below

ROW_N FamilyID CompanyName HierarchyLevel ClientID
1      A        XX1          0              A1
2      A        XX2          1              A12
3      A        XX3          2              A345
4      A        XX4          2              A456
5      A        XX5          3              A6788
6      A        XX6          2              A346
7      A        XX7          3              A6789
8      B        XX8          0              B1
9      B        XX9          1              B11 
10     B        X10          2              B345
11     B        X11          2              B567 
12     B        X12          3              B6789

We want to build parent-children relationships from the above structure to horizontal using ClientID as keys as per below.
The logic is the parent is the closest one level higher hierarchy order by ROW_N.

Hence expected output as per below

ROW_N FamilyID ParentID ParentName ChildrenID ChildrenName HierarchyLevel 
1      A        A1         XX1        A12        XX2          1
2      A        A12        XX2        A345       XX3          2
3      A        A12        XX2        A456       XX4          2
4      A        A456       XX4        A6788      XX5          3
5      A        A12        XX2        A346       XX6          2
6      A        A346       XX6       A6789       XX7          3
7      B        B1         XX8        B11        XX9          1
8      B        B11        XX9        B345       X10          2
9      B        B11        XX9        B567       X11          2
10     B        B567        X11       B6789      X12          3

 

[Chris Miller]

First read in all data with a simple SELECT * FROM yourtable into a List or an Array and then go from there.

For example as shown here and you find tons of examples for c# connecting with SQL Server:

https://stackoverflow.com/questions/21709305/how-to-directly-execute-sql-query-in-c

I personally find this Python example simpler, but it depends whether you're more familiar with C# or Python programming:

https://docs.devart.com/odbc/sqlserver/python.htm

Skip Step2, of course, you want to get data from a simple SELECT * FROM yourtable.

The simplicity here might also be too simple, as it reads in records and just prints rows. C# examples most of the time work with defining objects that have typed fields for the database columns to read in an array or collection of objects. In general, when using a reader object the columns are available row by row you fetch and columns are available as reader["columnname"] in the data type it has in SQL Server.

If you look into my tek-tips profile you can see I'd do this with FoxPro, but no need to learn something completely new, of course.

Tha major advantage once you read something into an array, is that you can access any row and column in any order you want, can read values of two succcessive rows without learning the complex clauses a LAG or LEAD access to columns needs.
Edit: It's of course fine to export data into a CSV file, in general I'd recommend you learn database access from python or C#, because it is how databases are meant to be used, by a client and a general programming language. When processing database internally within the DBC with means of SQL scripts becomes hard, even for experts, then of course it is still simple to fetch data from a database and process it with other means.

Chriss

 

[mikrom]

I can write c#/python, would you tell me how the best logic to solve the problem?

The logic is not complicated.

A simple tool for solving problems like this is awk.
It works with text files. So you can first export your data from the table into a CSV file, then run a script which processes this file and creates resulting CSV file and then finally import this result into a database table.

I posted an example solution here in awk forum - you can try it:

https://www.tek-tips.com/viewthread.cfm?qid=1829233

 

[mikrom]

Hi peac3,
I posted an example to your question in C# forum here:

https://www.tek-tips.com/viewthread.cfm?qid=1829224