Pivot Data Based on Hierarchy

Question

I have a hierarchical data whose structure is subject to change. The relationships are maintained in a single table that are identified by self referencing on two columns, the node ID and a Parent ID. I would like to be able run a query to pivot the data so that each row represents the lowest unit of the nodes.

For example:

If I have a table that looks like this...

I would like to be able to get to this...

I've played around with doing several joins as an attempt to get everything on the same line...

SELECT L1.NAME AS CITY, L2.NAME AS COUNTY, L3.NAME AS STATE, L4.NAME AS 
COUNTRY
FROM TABLENAME L1
LEFT JOIN TABLENAME AS L2 ON L1.PARENT_NODE_ID = L2.NODE_ID
LEFT JOIN TABLENAME AS L3 ON L2.PARENT_NODE_ID = L3.NODE_ID
LEFT JOIN TABLENAME AS L4 ON L3.PARENT_NODE_ID = L4.NODE_ID
WHERE L1.Type = City

Here is the heart of the question: I may not always know the structure of the hierarchy. Thus I need a solution that could handle changes. Say the keepers of the business logic decide that we need to add Hemisphere above country. Or a Region (West Coast, Central, East Coast) above State. City however will always be the lowest node. I need something that can exist independent of the hierarchical structure.

UPDATE My original question I used a simple example. In my actual solution I must leverage several joins to get the hierarchy that I need. I am working on the bellow query but as of now it returns null for every column that I wish to populate. Most likely an issue with the case statements?

;WITH ALLORGS AS( --All Orgs
    SELECT ORGS.ID, ORGS.ORG_NAME
        , HIER.ID_PARENTORG, TYP.ORG_TYPE_DESCR
        FROM ORGANIATIONS AS ORGS
        FULL OUTER JOIN HIERARCHYTABLE AS HIER ON ORGS.ID = HIER.ID_ORG
        FULL OUTER JOIN ORGANIZATION_TYPES AS TYP ON ORGS.ID_ORG_TYPE = TYP.ID

), CTE AS ( 

    SELECT ID
    , ID_PARENTORG
    , L1.ORG_NAME 
    --, ORG_TYPE_DESCR
    , CAST('' as varchar(100)) AS UNIT
    , CAST('' as varchar(100)) AS REGION
    , CAST('' as varchar(100)) AS DDA_POOL
    , CAST('' as varchar(100)) AS COUNTY
    , CAST('' as varchar(100)) AS STATE
    , CAST('' as varchar(100)) AS BUSINESS_UNIT
    , CAST('' as varchar(100)) AS PROEPRTY
    , CAST('' as varchar(100)) AS DISTRICT
    , 1 AS FLAG

    FROM ALLORGS L1
    WHERE L1.ORG_TYPE_DESCR = 'COST CENTER'

    UNION ALL

    SELECT T1.ID
    ,L2.ID_PARENTORG
    ,T1.ORG_NAME AS COSTCNTR
    --, T.ORG_TYPE_DESCR
    ,CASE WHEN L2.ORG_TYPE_DESCR = 'UNIT' THEN L2.ORG_NAME ELSE NULL END AS UNIT
    ,CASE WHEN L2.ORG_TYPE_DESCR = 'REGION' THEN L2.ORG_NAME ELSE NULL END AS REGION
    ,CASE WHEN L2.ORG_TYPE_DESCR = 'DDA_POOL' THEN L2.ORG_NAME ELSE NULL END AS DDA_POOL
    ,CASE WHEN L2.ORG_TYPE_DESCR = 'COUNTRY' THEN L2.ORG_NAME ELSE NULL END AS COUNTRY
    ,CASE WHEN L2.ORG_TYPE_DESCR = 'STATE' THEN L2.ORG_NAME ELSE NULL END AS STATE
    ,CASE WHEN L2.ORG_TYPE_DESCR = 'BUSINESS_UNIT' THEN L2.ORG_NAME ELSE NULL END AS BUSINESS_UNIT
    ,CASE WHEN L2.ORG_TYPE_DESCR = 'PROPERTY' THEN L2.ORG_NAME ELSE NULL END AS PROPERTY
    ,CASE WHEN L2.ORG_TYPE_DESCR = 'DISTRICT' THEN L2.ORG_NAME ELSE NULL END AS DISTRICT
    ,T1.FLAG + 1 AS FLAG

    FROM CTE AS T1 
    INNER JOIN ALLORGS AS L2 ON T1.ID_PARENTORG = L2.ID 
)
SELECT a.ID
,a.ORG_NAME AS COSTCNTR
,UNIT
,REGION
,DDA_POOL
,COUNTY
,STATE
,BUSINESS_UNIT
,PROEPRTY
,DISTRICT
FROM CTE AS a
INNER JOIN (SELECT ID, MAX(FLAG) FLAG FROM CTE GROUP BY ID) b ON a.ID = b.ID AND a.FLAG = b.FLAG  

Answer 1

Try this... Please test it with more sample data before using it.

Table Script and Sample data

CREATE TABLE [TableName](
    [ParentNodeID] [int] NULL,
    [NodeID] [int] NULL,
    [Type] [nvarchar](50) NULL,
    [Name] [nvarchar](50) NULL
) 

INSERT [TableName] ([ParentNodeID], [NodeID], [Type], [Name]) VALUES (NULL, 1, N'Country', N'US')
INSERT [TableName] ([ParentNodeID], [NodeID], [Type], [Name]) VALUES (1, 2, N'State', N'Texas')
INSERT [TableName] ([ParentNodeID], [NodeID], [Type], [Name]) VALUES (2, 3, N'County', N'Dallas')
INSERT [TableName] ([ParentNodeID], [NodeID], [Type], [Name]) VALUES (3, 4, N'City', N'Dallas')
INSERT [TableName] ([ParentNodeID], [NodeID], [Type], [Name]) VALUES (NULL, 1, N'Country', N'US')
INSERT [TableName] ([ParentNodeID], [NodeID], [Type], [Name]) VALUES (5, 6, N'State', N'Massachusetts')
INSERT [TableName] ([ParentNodeID], [NodeID], [Type], [Name]) VALUES (7, 8, N'County', N'Suffolk')
INSERT [TableName] ([ParentNodeID], [NodeID], [Type], [Name]) VALUES (9, 10, N'City', N'Boston')

Query

DECLARE @cols AS NVARCHAR(max) = Stuff((SELECT DISTINCT ',' + Quotename([Type])
         FROM   TableName       
         FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); 

DECLARE @query AS NVARCHAR(max) =  'SELECT max(NodeID)    AS NodeID
                                          ,max([Country]) AS Country
                                          ,max([State])   AS STATE
                                          ,max([County])  AS County
                                          ,max([City])    AS City
                                    FROM (
                                        SELECT *, Row_Number() OVER (PARTITION BY Type ORDER BY NodeID) rn
                                        FROM TableName
                                        ) sq
                                    pivot(max([Name]) FOR [Type] IN ('+ @cols +') ) pvt
                                    GROUP BY rn';

EXECUTE(@query) 

Output

+--------+---------+---------------+---------+--------+
| NodeID | Country |     STATE     | County  |  City  |
+--------+---------+---------------+---------+--------+
|      4 | US      | Texas         | Dallas  | Dallas |
|     10 | US      | Massachusetts | Suffolk | Boston |
+--------+---------+---------------+---------+--------+

Online Demo: http://www.sqlfiddle.com/#!18/7470b/3/0

Answer 2

I also have implemented the same situation, I haven't test this as I only use my phone here now, but pretty much the logic i used is like this, using CTE that doing reccursive, hope this also works

WITH CTE AS (
--Put Initial Value '' to be filled later
SELECT NODE_ID, PARENT_ID, L1.NAME AS CITY, '' AS COUNTY, '' AS STATE, '' AS COUNTRY,
--add hemisphere
'' AS HEMISPHERE,
1 AS FLAG --Only for indication of looping
FROM TABLENAME L1
WHERE L1.TYPE = 'CITY'

UNION ALL

SELECT T1.NODE_ID, L2.PARENT_ID, 
T1.NAME AS CITY, 
(CASE WHEN L2.TYPE = 'COUNTY' THEN L2.NAME ELSE T1.NAME) AS COUNTY, 
(CASE WHEN L2.TYPE = 'STATE' THEN L2.NAME ELSE T1.NAME) AS STATE, 
(CASE WHEN L2.TYPE = 'COUNTRY' THEN L2.NAME ELSE T1.NAME) AS COUNTRY
--and can add some more columns here,  in case if there is additional column for Hemisphere
 (CASE WHEN L2.TYPE = 'Hemisphere' THEN L2.NAME ELSE T1.NAME) AS Hemisphere
T1.FLAG + 1 AS FLAG -- add +1 for n reccuring, only for indication of looping
FROM CTE T1
INNER JOIN TABLENAME L2 ON T1.PARENT_ID = 
L2.NODE_ID
)

SELECT a.NODE_ID, CITY, COUNTY, STATE, COUNTRY
FROM CTE a
--to get the last loop which has completely filled data
INNER JOIN (SELECT NODE_ID, MAX(FLAG) FLAG FROM CTE GROUP BY NODE_ID ) b ON a.NODE_ID = b.NODE_ID AND a.FLAG = b.FLAG