Link to home
Create AccountLog in
Avatar of brandonh6k

asked on

Pivoting Hierarchical Structure... with a twist

I have a table that store hierarchical information.  Standard ID <-> ParentID type table (assume ID, ParentID, Type, and Name columns).  There are 3 types of objects in this structure (Organizations, Groups and Projects).  I need to flatten this structure out for usage in a data warehousing environment.  The business logic associated with the hierarchy is that Orgs can contain (i.e. have as direct childen) Groups or Projects, Groups can contain both Groups or Projects and Projects can contain projects (i.e. subprojects).  This means you can't have a group underneath a project in the tree.

I figured out how to flatten this information out initially by doing a series of left joins from the table onto itself, but that required me to have a table that looked like this:

CurrentUnitID - CurrentUnitName - Parent1ID - Parent1Name - Parent1Type - Parent2ID - Parent2Name - Parent2Type - Parent3ID - Parent3Name - Parent3Type

The twist is... I want to be able to flatten it out into a table that looks similar to this (so I don't have to track the type for each level):

CurrentUnitID - CurrentUnitName - OrganizationID - OrganizationName - Group1ID - Group1Name - Group2ID - Group2Name - Project1ID - Project1Name - Project2ID - Project2Name

So that for any given unit in the hierarchy, you can look at the project1 fields and see what project is it's immediate parent.  If the current unit's immediate parent isn't a project, that field is null.  Same concept for the GroupX fields.

This is SQL Server 2005.  I can use raw T-SQL to do it or SQL Server Integration Services (SSIS).  I'd prefer an Integration Services answer if possible.

Any ideas?  Thanks!
Avatar of Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

sorry post you left outer example and some sample data
Avatar of brandonh6k

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account