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 
