Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Manipulate Horizontal Hierarchy with UNPIVOT

By Bernabe Diaz,

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

Total article views: 909 | Views in the last 30 days: 9
 
Related Articles
FORUM

Where is place to store description of Columns?

Looking for table that storing information of column description

FORUM

Column Description

Is there a way to get the column description from the tables in a db? I created a script to get t...

FORUM

Column description

Is there a way in T-sql get the defined COLUMN DESCRIPTION text (One of the Column properties when y...

FORUM

Hresult: 0x80004005 Description:

Hresult: 0x80004005 Description:

ARTICLE

Get Error Description in SQL Server 2000

How to Extract Error Description in SQL Server 2000 based on Error Number

Tags
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones