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

Look up dynamic pivot or dynamic cross tab. It is two ways of handling this and there are dozens and dozens if not hundreds of examples just on SO. — Sean Lange, May 11 '18 at 16:39
Here is one such example. https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — Sean Lange, May 11 '18 at 16:40
It seems your table data is not correct could you please, check once again. reference Ids are missing. — Rajat Jaiswal, May 11 '18 at 16:45

DxTx · Answer 1 · 2018-05-11T17:19:29.243

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

Alfin E. R. · Accepted Answer · 2018-05-15T03:21:56.517

3

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

edited May 15 '18 at 03:21

answered May 11 '18 at 17:14

Alfin E. R.

741
1
7
24

This has promise! I am trying to work my actual scenario into this. I provided a simple example for the question, in reality, my table is a group joins as the values of the Hierarchy Table exist mainly as coded values. In other words "TABLENAME" as seen above is actually an aggregation of 3 or 4 full outer joins. – LCaraway May 11 '18 at 20:13
@LCaraway, thank you, i think the recursive keep doing it's loop as long as there is match between parents and node id, it's a little bit tricky though, the columns of select statement at the top and columns of select statement at the bottom of "UNION ALL" must has similar column names, and we necessarily to be careful and pay attention into the aliases of the table, which columns from which tables are we about to take for the right value of columns. :) I hope this works – Alfin E. R. May 12 '18 at 03:26
I have just edited the answer and add Hemisphere column at the top select statement, – Alfin E. R. May 12 '18 at 03:26
In the Select Statement After Union ALL Should it be `L2.Name` rather than `L1.Name` – LCaraway May 14 '18 at 20:46
Or `T1.Name` maybe – LCaraway May 14 '18 at 21:29
Oh sorry it supposed to be T1, because T1 already took the city name at the first loop (before union all) – Alfin E. R. May 15 '18 at 03:21
Ok, i have this really close using the Code I posted in my update. I think there is something wrong with the final Select statement however. If i select all from CTE I can confirm that the values get populated except that they return a new row for that column. The next trick here is to get them all aligned. Selecting the MAX(FLAG) only populates a single column. – LCaraway May 15 '18 at 14:56

Pivot Data Based on Hierarchy

2 Answers2