Technical Article

Manipulate Horizontal Hierarchy with UNPIVOT

,

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

/* first the sample table with its records */IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myHorizontalHierarchy]') AND type in (N'U'))
DROP TABLE [dbo].[myHorizontalHierarchy]
GO
CREATE TABLE myHorizontalHierarchy(
[HierarchyID] INT IDENTITY(1,1) NOT NULL,
[City_ID][nvarchar](5) NOT NULL,
[Area_ID][nvarchar](50) NOT NULL,
[Manager_ID][nvarchar](50) NOT NULL,
[director_ID][nvarchar](50) NOT NULL,
[sub_director_ID][nvarchar](50) NOT NULL,
[AreaOfficer_ID][nvarchar](50) NOT NULL,
[AreaOfficer1][nvarchar](50) NOT NULL,
[AreaOfficer2][nvarchar](50) NOT NULL,
[AreaOfficer3][nvarchar](50) NOT NULL,
[SalePersonSupervisor][nvarchar](50) NOT NULL,
[SalePerson][nvarchar](50) NOT NULL,
[SalePerson_level1][nvarchar](50) NOT NULL,
[customer_representative_ID][nvarchar](50) NOT NULL,
[City_Description][nvarchar](128) NULL,
[Area_Description][nvarchar](128) NULL,
[Manager_Description] [nvarchar](128) NULL,
[director_Description] [nvarchar](128) NULL,
[sub_director_Description] [nvarchar](128) NULL,
[AreaOfficer_Description] [nvarchar](128) NULL,
[AreaOfficer1_Description] [nvarchar](128) NULL,
[AreaOfficer2_Description] [nvarchar](128) NULL,
[AreaOfficer3_Description] [nvarchar](128) NULL,
[SalePersonSupervisor_Description] [nvarchar](128) NULL,
[SalePerson_Description] [nvarchar](128) NULL,
[SalePerson_level1_Description][nvarchar](128) NOT NULL,
[customer_representative_Description][nvarchar](128) NOT NULL,
[LOAD_DATE][datetime] NULL,
[LOAD_TIME][nvarchar](8) NULL,
 CONSTRAINT [PK_HierarchtID] PRIMARY KEY CLUSTERED 
(
[HierarchyID] ASC
)
) ON [PRIMARY]  

INSERT INTO [dbo].[myHorizontalHierarchy]
([City_ID],[Area_ID]  ,[Manager_ID],[director_ID],[sub_director_ID],[AreaOfficer_ID],[AreaOfficer1],[AreaOfficer2],[AreaOfficer3],[SalePersonSupervisor],[SalePerson],[SalePerson_level1],[customer_representative_ID],[City_Description],[Area_Description],[Manager_Description],[director_Description],[sub_director_Description],[AreaOfficer_Description],[AreaOfficer1_Description],[AreaOfficer2_Description],[AreaOfficer3_Description],[SalePersonSupervisor_Description],[SalePerson_Description],[SalePerson_level1_Description],[customer_representative_Description],[LOAD_DATE],[LOAD_TIME])
SELECT 'Palos','Moguer','Mgr One'   ,'Dctr One'   ,'sub_Dctr One','Sailor','Sailor 1','Sailor 2','Sailor 3','TripSalePerson','SalePerson','SalePerson1','TelePerson','This city is in Spain','Puerto de Palos','Trip Mgr forever ','Dctr  forever ','sub_Dctr if Dctr wants','Doing my job','NA','NA','NA','NA','NA','NA','NA',GETDATE(),CONVERT(VARCHAR(8),GETDATE(),114)
UNION ALL
SELECT 'ATL','ww','Mgr One','Dctr One','sub_Dctr One','Sailor','Sailor 1','Sailor 2','Sailor 3','TripSalePerson','SalePerson','SalePerson1','TelePerson','This city is in Spain','Puerto de Palos','Trip Mgr forever ','Dctr  forever ','sub_Dctr if Dctr wants','Doing my job','NA','NA','NA','NA','NA','NA','NA',GETDATE(),CONVERT(VARCHAR(8),GETDATE(),114)
UNION ALL
SELECT 'NYC','gg','Mgr One','Dctr One','sub_Dctr One','Sailor','Sailor 1','Sailor 2','Sailor 3','TripSalePerson','SalePerson','SalePerson1','TelePerson','This city is in Spain','Puerto de Palos','Trip Mgr forever ','Dctr  forever ','sub_Dctr if Dctr wants','Doing my job','NA','NA','NA','NA','NA','NA','NA',GETDATE(),CONVERT(VARCHAR(8),GETDATE(),114)
UNION ALL
SELECT 'MIA','OpaLocka','Mgr One','Dctr One','sub_Dctr One','Sailor','Sailor 1','Sailor 2','Sailor 3','TripSalePerson','SalePerson','SalePerson1','TelePerson','This city is in Spain','Puerto de Palos','Trip Mgr forever ','Dctr  forever ','sub_Dctr if Dctr wants','Doing my job','NA','NA','NA','NA','NA','NA','NA',GETDATE(),CONVERT(VARCHAR(8),GETDATE(),114)
UNION ALL
SELECT 'STF','wao','Mgr One','Dctr One','sub_Dctr One','Sailor','Sailor 1','Sailor 2','Sailor 3','TripSalePerson','SalePerson','SalePerson1','TelePerson','This city is in Spain','Puerto de Palos','Trip Mgr forever ','Dctr  forever ','sub_Dctr if Dctr wants','Doing my job','NA','NA','NA','NA','NA','NA','NA',GETDATE(),CONVERT(VARCHAR(8),GETDATE(),114)


/* then the select script with our UNPIVOT friend */
/*
Author:Bernabe Diaz
Date created: 10/23/2011
 
Asumptions: input table ([dbo].[myHorizontalHierarchy]) contains flat hierarchy (horizontal)
Exist a one to One relationshipt between Level colum and level description
and it is completely identified by the level ID Number  in the format 
for colum level LVL_<level ID Number>_ID
for column Level description LVL_<level ID Number>_DESCR

*/

CREATE TABLE #TEMP_HIER_HZ(
[HierarchyID] INT IDENTITY(1,1) NOT NULL,
[City_ID][nvarchar](5) NOT NULL,
[Area_ID][nvarchar](50) NOT NULL,
[LVL_00_ID] [nvarchar](50) NOT NULL,
[LVL_01_ID] [nvarchar](50) NOT NULL,
[LVL_02_ID] [nvarchar](50) NOT NULL,
[LVL_03_ID] [nvarchar](50) NOT NULL,
[LVL_04_ID] [nvarchar](50) NOT NULL,
[LVL_05_ID] [nvarchar](50) NOT NULL,
[LVL_06_ID] [nvarchar](50) NOT NULL,
[LVL_07_ID] [nvarchar](50) NOT NULL,
[LVL_08_ID] [nvarchar](50) NOT NULL,
[LVL_09_ID] [nvarchar](50) NOT NULL,
[LVL_10_ID] [nvarchar](50) NOT NULL,
[LVL_00_DESCR][nvarchar](128) NULL,
[LVL_01_DESCR][nvarchar](128) NULL,
[LVL_02_DESCR][nvarchar](128) NULL,
[LVL_03_DESCR][nvarchar](128) NULL,
[LVL_04_DESCR][nvarchar](128) NULL,
[LVL_05_DESCR][nvarchar](128) NULL,
[LVL_06_DESCR][nvarchar](128) NULL,
[LVL_07_DESCR][nvarchar](128) NULL,
[LVL_08_DESCR][nvarchar](128) NULL,
[LVL_09_DESCR][nvarchar](128) NULL,
[LVL_10_DESCR][nvarchar](128) NULL,
[LOAD_DATE][datetime] NULL,
[LOAD_TIME][nvarchar](8) NULL,
 CONSTRAINT [tempPK_HierarchtID] PRIMARY KEY CLUSTERED 
(
[HierarchyID] ASC
)
)



INSERT INTO #TEMP_HIER_HZ

SELECT 
 [City_ID][City_ID]
,[Area_ID][Area_ID]
,
[Manager_ID][LVL_00_ID] 
,[director_ID][LVL_01_ID] 
,[sub_director_ID][LVL_02_ID] 
,[AreaOfficer_ID][LVL_03_ID] 
,[AreaOfficer1][LVL_04_ID] 
,[AreaOfficer2][LVL_05_ID] 
,[AreaOfficer3][LVL_06_ID] 
,[SalePersonSupervisor][LVL_07_ID] 
,[SalePerson][LVL_08_ID] 
,[SalePerson_level1][LVL_09_ID] 
,[customer_representative_ID][LVL_10_ID] 
,[Manager_Description] [LVL_00_DESCR]
,[director_Description] [LVL_01_DESCR]
,[sub_director_Description] [LVL_02_DESCR]
,[AreaOfficer_Description] [LVL_03_DESCR]
,[AreaOfficer1_Description] [LVL_04_DESCR]
,[AreaOfficer2_Description] [LVL_05_DESCR]
,[AreaOfficer3_Description] [LVL_06_DESCR]
,[SalePersonSupervisor_Description] [LVL_07_DESCR]
,[SalePerson_Description] [LVL_08_DESCR]
,[SalePerson_level1_Description][LVL_09_DESCR]
,[customer_representative_Description][LVL_10_DESCR]
,[LOAD_DATE]
,[LOAD_TIME]
  
      
  FROM [dbo].[myHorizontalHierarchy]
GO




SELECT [HierarchyID], 
 [City_ID]
,[Area_ID]

,ROW_NUMBER()OVER(Partition By [HierarchyID] Order By levels) as tempLevelId, 
levelID, 
LevDescription INTO #final
FROM 
( 
SELECT [HierarchyID],
 [City_ID]
,[Area_ID],LVL_00_ID,LVL_01_ID,LVL_02_ID,LVL_03_ID,LVL_04_ID,LVL_05_ID
,LVL_06_ID,LVL_07_ID,LVL_08_ID,LVL_09_ID,LVL_10_ID
,LVL_00_DESCR,LVL_01_DESCR,LVL_02_DESCR,LVL_03_DESCR,LVL_04_DESCR
,LVL_05_DESCR,LVL_06_DESCR,LVL_07_DESCR,LVL_08_DESCR
,LVL_09_DESCR,LVL_10_DESCR
FROM #TEMP_HIER_HZ
) FlatHchyBody
UNPIVOT 
( 
levelID FOR levels IN (
LVL_00_ID,LVL_01_ID,LVL_02_ID,LVL_03_ID,LVL_04_ID,LVL_05_ID
,LVL_06_ID,LVL_07_ID,LVL_08_ID,LVL_09_ID,LVL_10_ID
)
) lvlID 
UNPIVOT 
( 
LevDescription For LevelsDesc IN ( LVL_00_DESCR,LVL_01_DESCR,LVL_02_DESCR,LVL_03_DESCR,LVL_04_DESCR,
LVL_05_DESCR,LVL_06_DESCR,LVL_07_DESCR,LVL_08_DESCR
,LVL_09_DESCR,LVL_10_DESCR
) 
) LevDesc 

WHERE SUBSTRING(levels,5,2)=SUBSTRING(LevelsDesc,5,2)


SELECT DISTINCT [HierarchyID], [City_ID]
,[Area_ID], tempLevelId, 
levelID ID , 
LevDescription DESCRIP,
CASE WHEN tempLevelId=1 THEN NULL ELSE
(SELECT levelID 
FROM #final p 
WHERE p.[HierarchyID]=o.[HierarchyID] AND (p.tempLevelId)=o.tempLevelId-1) 
END AS PARENT
INTO #LASTFINAL
FROM #final o
ORDER BY [HierarchyID], tempLevelId

SELECT   [City_ID]
,[Area_ID],ID,DESCRIP,PARENT
FROM #LASTFINAL

DROP TABLE #final
DROP TABLE #LASTFINAL
DROP TABLE #TEMP_HIER_HZ

Rate

4 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (1)

You rated this post out of 5. Change rating