Sometimes you need to provide a vertical hierarchy from a source that represents the hierrarchy horizontally and also you need to catch additional columns (descriptions ) that come with it.
That situation is common having the source from a ERP flat hierarchy table.
Or in some other cases errors during an extraction process to capture the ofender column and the column value.
For example
EmployeeLevel1, FullName1,EmployeeLevel2, FullName2, ........,
in this case Level1>Level2> .......
You need
EmployeeID, Fullname, Supervisor
or generically
ID, Description, Parent.
Also, you can have several description columns.
We can resolve this task by using UNPIVOT operator repeated to the number of columns description you have.
Lets create a sample table and insert some data
and then the script to handle the task
We will use an arbitrary (columns name) table and use a fixed layout view to match the columns ID to its description.
Also Parent equal NULL should be the root
The key to match the columns is the condition (in the script)
expressed by:
SUBSTRING (levels,5,2)=SUBSTRING(LevelsDesc,5,2)
You can adapt it to your needs.
Enjoy it
