Pivoting hierarchial data

Question

Pivoting hierarchial data

Jeff Shurak

SSCrazy

Points: 2361
More actions
December 10, 2014 at 12:36 pm

#397759

I'm having some issues getting this one. I have a table that has a hierarchical employee structure:
create table hierarchy (
emplid int
,First_Name varchar(128)
,Last_Name varchar(128)
,Report_Emplid int
,Manager_Level smallint
,Manager_Descr varchar(128)
)
insert into hierarchy values (11234,'Sue','Que',1138,9,'Non-Manager')
insert into hierarchy values (1138,'Sally','Field',4362,7,'Supervisor')
insert into hierarchy values (4362,'John','Smith',3279,5,'Manager')
insert into hierarchy values (3279,'Debra','Jones',4537,1,'Director')
insert into hierarchy values (4537,'Henry','Ford',9458,3,'VP')
insert into hierarchy values (9458,'Val','Pal',5943,13,'Sr. VP')
insert into hierarchy values (5943,'Paul','Simon',5943,0,'CEO')
I'm trying pivot to get the hierarchy on a single row an output to be emplid,supervisor's emplid,manager emplid,director emplid,vp emplid, sr.vp emplid, ceo emplid. The pivot I have below successfully pivots, but because multiple emplid's, there's a row for each ID and only displays they're director report. Is there a way to do this with a simple pivot?
select Emplid,[supervisor],[manager],[director],[vp],[Sr. VP],[CEO]
from hierarchy
pivot(max(report_emplid) for manager_descr in([supervisor],[manager],[director],[vp],[Sr. VP],[CEO])) as p

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

aaron.reese SSChampion Points: 13498 More actions · Answer 1

Hi Jeff. it's a bit late in the day for me to get my head around the whole result but the technique you are looking for uses STUFF and FORXML. You will find lots of examples on SQLServerCentral and the interweb in general.

Basically FOR XML turns the results set into a single text block with XML nested tags and the STUFF replaces the tags with whatever code you want.

I have used the technique in the past to identify unique customer compensation journeys where a customer could have an unlimited number of events in any chronological order and I needed to see the whole journey as a single delimited string and then use that string as the GROUP BY clause to count the number of customers with that journey.

The code is horrible (it always is when XML gets involved) but the execution is surprisingly fast.

Jeff Shurak SSCrazy Points: 2361 More actions · Answer 2

Thanks for the lead, I'll take a look in that direction.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

This isn't pretty, but it might be what you need to handle one to many relationships between managers and employees.

SELECT CEO.Emplid AS CEO,

SrVP.Emplid AS SrVP,

VP.Emplid AS VP,

Dir.Emplid AS Dir,

Mgr.Emplid AS Mgr,

Sup.Emplid AS Sup,

NoMgr.Emplid AS NoMgr

FROM Hierarchy CEO

JOIN Hierarchy SrVP ON CEO.Emplid = SrVP.Report_Emplid

JOIN Hierarchy VP ON SrVP.Emplid = VP.Report_Emplid

JOIN Hierarchy Dir ON VP.Emplid = Dir.Report_Emplid

JOIN Hierarchy Mgr ON Dir.Emplid = Mgr.Report_Emplid

JOIN Hierarchy Sup ON Mgr.Emplid = Sup.Report_Emplid

JOIN Hierarchy NoMgr ON Sup.Emplid = NoMgr.Report_Emplid

WHERE CEO.Emplid = CEO.Report_Emplid

AND SrVP.Emplid <> SrVP.Report_Emplid;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 4

Luis Cazares (12/10/2014)
This isn't pretty, but it might be what you need to handle one to many relationships between managers and employees.

Has the appeal of Cusano Rojo:rolleyes:,but it works

😎

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 5

Jeff Shurak (12/10/2014)
I'm trying pivot to get the hierarchy on a single row an output to be emplid,supervisor's emplid,manager emplid,director emplid,vp emplid, sr.vp emplid, ceo emplid. The pivot I have below successfully pivots, but because multiple emplid's, there's a row for each ID and only displays they're director report. Is there a way to do this with a simple pivot?

Do you actually need the output to have 1 column per position in the "upline" or can it be a single delimited column?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Shurak SSCrazy Points: 2361 More actions · Answer 6

Jeff, the output could be a single delimited line.

This is a 'one time thing' so I ended up getting it to work with something similar to Luis's suggestion. I'm sure we are all aware of how often one time things can turn into all the time things, so I'm certainly interested in finding an optimal solution.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 7

Understood.

Here's the concatenated ID's thing. The only thing that I needed to change in the original data is that the CEO cannot report to him/herself. The CEO reports to the mythical NULL manager.

CREATE TABLE #Heirarchy

(

EmplID INT

,First_Name VARCHAR(128)

,Last_Name VARCHAR(128)

,Report_Emplid INT

,Manager_Level SMALLINT

,Manager_Descr VARCHAR(128)

)

;

INSERT INTO #Heirarchy

VALUES (11234,'Sue' ,'Que' ,1138,9 ,'Non-Manager')

,(1138 ,'Sally','Field',4362,7 ,'Supervisor')

,(4362 ,'John' ,'Smith',3279,5 ,'Manager')

,(3279 ,'Debra','Jones',4537,1 ,'Director')

,(4537 ,'Henry','Ford' ,9458,3 ,'VP')

,(9458 ,'Val' ,'Pal' ,5943,13,'Sr. VP')

,(5943 ,'Paul' ,'Simon',NULL,0 ,'CEO')

;

WITH

cteDirectReports AS

(

SELECT *, EmployeeLevel = 1,

HierarchicalPath = CAST('\'+CAST(EmplID AS VARCHAR(10))+'\' AS VARCHAR(4000))

FROM #Heirarchy

WHERE Report_Emplid IS NULL

UNION ALL

SELECT e.*, EmployeeLevel = d.EmployeeLevel + 1,

HierarchicalPath = CAST(d.HierarchicalPath +CAST(e.EmplID AS VARCHAR(10))+'\' AS VARCHAR(4000))

FROM #Heirarchy e

INNER JOIN cteDirectReports d ON e.Report_Emplid = d.EmplID

)

SELECT *

FROM cteDirectReports

ORDER BY HierarchicalPath

;

Here's what the output from that looks like...

EmplID First_Name Last_Name Report_Emplid Manager_Level Manager_Descr EmployeeLevel HierarchicalPath

------ ---------- --------- ------------- ------------- ------------- ------------- -------------------------------------

5943 Paul Simon NULL 0 CEO 1 \59439458 Val Pal 5943 13 Sr. VP 2 \5943\94584537 Henry Ford 9458 3 VP 3 \5943\9458\45373279 Debra Jones 4537 1 Director 4 \5943\9458\4537\32794362 John Smith 3279 5 Manager 5 \5943\9458\4537\3279\43621138 Sally Field 4362 7 Supervisor 6 \5943\9458\4537\3279\4362\113811234 Sue Que 1138 9 Non-Manager 7 \5943\9458\4537\3279\4362\1138\11234

You can find an explanation of how it works (and a lot more) at the following URL. The article there will also lead you to two other articles that will make your life a whole lot easier if you need to work with this type of "Adjacency List" or "Parent\Child" hierarchy for something else.

http://www.sqlservercentral.com/articles/T-SQL/72503/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 8

I had a requirement a few years ago to do something similar where the flattened hierarchy where each level was a column, This is the thread http://www.sqlservercentral.com/Forums/Topic1071035-392-1.aspx the end solution posted in the file BuildHierarchy.txt in the second from bottom post of the first page.

That's rather targeted for that one client, and had a few quirks based on their original data set, but the basis is in the 4th post.

_________________________________________________________________________
SSC Guide to Posting and Best Practices