How to flatten a normalised hierarchy table?

Question

Post reply

How to flatten a normalised hierarchy table?

j.gale1

SSC Veteran

Points: 202

May 2, 2017 at 8:56 pm

#323271

I have an OrgChart table that looks something like this:

PositionCode   PositionName   ParentCode  LevelAA0001         CEO                        0AA0002         CFO            AA0001      1AA0003         CIO            AA0002      2AA0004         SnrMgr         AA0002      2AA0005         JnrMgr         AA0004      3AA0006         ItMgr          AA0003      3

etc

I need to convert this to a flattened structure like this (for a BI solution):
(CurCode is the position we're focusing on for that row, followed by the org hierarchy above that position)

CurCode  Lvl0Code Lvl0Name Lvl1Code Lvl1Name Lvl2Code Lvl2Name Lvl3Code Lvl3NameAA0001   AA0001   CEO      na       na       na       na       na       naAA0002   AA0001   CEO      AA0002   CFO      na       na       na       naAA0003   AA0001   CEO      AA0002   CFO      AA0003   CIO      na       naAA0004   AA0001   CEO      AA0002   CFO      AA0004   SnrMgr   na       naAA0005   AA0001   CEO      AA0002   CFO      AA0004   SnrMgr   AA0005   JnrMgrAA0006   AA0001   CEO      AA0002   CFO      AA0003   CIO      AA0006   ItMgr

How can I achieve that in T-SQL?
The table goes 5 levels deep, so I don't need an undefined number of columns.
I was looking at a PIVOT, but couldn't see how to make it work properly.
Any help will be very appreciated.

Viewing 3 posts - 1 through 3 (of 3 total)

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

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

j.gale1 - Tuesday, May 2, 2017 8:56 PM
I have an OrgChart table that looks something like this:
PositionCode   PositionName   ParentCode  LevelAA0001         CEO                        0AA0002         CFO            AA0001      1AA0003         CIO            AA0002      2AA0004         SnrMgr         AA0002      2AA0005         JnrMgr         AA0004      3AA0006         ItMgr          AA0003      3
etc
I need to convert this to a flattened structure like this (for a BI solution):
(CurCode is the position we're focusing on for that row, followed by the org hierarchy above that position)
CurCode  Lvl0Code Lvl0Name Lvl1Code Lvl1Name Lvl2Code Lvl2Name Lvl3Code Lvl3NameAA0001   AA0001   CEO      na       na       na       na       na       naAA0002   AA0001   CEO      AA0002   CFO      na       na       na       naAA0003   AA0001   CEO      AA0002   CFO      AA0003   CIO      na       naAA0004   AA0001   CEO      AA0002   CFO      AA0004   SnrMgr   na       naAA0005   AA0001   CEO      AA0002   CFO      AA0004   SnrMgr   AA0005   JnrMgrAA0006   AA0001   CEO      AA0002   CFO      AA0003   CIO      AA0006   ItMgr
How can I achieve that in T-SQL?
The table goes 5 levels deep, so I don't need an undefined number of columns.
I was looking at a PIVOT, but couldn't see how to make it work properly.
Any help will be very appreciated.

Something happened to your sample data. To get better answers, you should post it as CREATE TABLE and INSERT statements. That way we can just copy and work directly on it without problems.

The easiest way to do what you're asking for is to JOIN the table to itself several times. e.g.
SELECT * FROM Table lvl0 JOIN Table lvl1 ON lvl0.SomeID = lvl1.SomeParentID JOIN Table lvl2 ON lvl1.SomeID = lvl2.SomeParentID JOIN Table lvl3 ON lvl2.SomeID = lvl3.SomeParentID JOIN Table lvl4 ON lvl3.SomeID = lvl4.SomeParentID;

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

Jeff Moden SSC Guru Points: 1004649 More actions · Answer 2

j.gale1 - Tuesday, May 2, 2017 8:56 PM
I have an OrgChart table that looks something like this:
PositionCode   PositionName   ParentCode  LevelAA0001         CEO                        0AA0002         CFO            AA0001      1AA0003         CIO            AA0002      2AA0004         SnrMgr         AA0002      2AA0005         JnrMgr         AA0004      3AA0006         ItMgr          AA0003      3
etc
I need to convert this to a flattened structure like this (for a BI solution):
(CurCode is the position we're focusing on for that row, followed by the org hierarchy above that position)
CurCode  Lvl0Code Lvl0Name Lvl1Code Lvl1Name Lvl2Code Lvl2Name Lvl3Code Lvl3NameAA0001   AA0001   CEO      na       na       na       na       na       naAA0002   AA0001   CEO      AA0002   CFO      na       na       na       naAA0003   AA0001   CEO      AA0002   CFO      AA0003   CIO      na       naAA0004   AA0001   CEO      AA0002   CFO      AA0004   SnrMgr   na       naAA0005   AA0001   CEO      AA0002   CFO      AA0004   SnrMgr   AA0005   JnrMgrAA0006   AA0001   CEO      AA0002   CFO      AA0003   CIO      AA0006   ItMgr
How can I achieve that in T-SQL?
The table goes 5 levels deep, so I don't need an undefined number of columns.
I was looking at a PIVOT, but couldn't see how to make it work properly.
Any help will be very appreciated.

Consider a possibly more useful structure for BI purposes. Please see the following article. The alternative structure keeps you're original "Adjacency List" (parent/child structure) and pre-aggregates most of the questions that people have within such hierarchies. And, it's nasty fast.
http://www.sqlservercentral.com/articles/T-SQL/94570/

--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)