sql - 基于层次结构的透视数据

Question

我有一个分层数据，其结构可能会发生变化。这些关系维护在一个表中，该表通过两列(节点 ID 和父 ID)上的自引用来标识。我希望能够运行查询来旋转数据，以便每一行代表节点的最低单元。

例如:

如果我有一张像这样的 table ......

我希望能够做到这一点...

我尝试过进行多次连接，试图让所有内容都在同一行...

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

这是问题的核心:我可能并不总是知道层次结构的结构。因此我需要一个可以处理变化的解决方案。假设业务逻辑的维护者决定我们需要在国家/地区之上添加半球。或州上方的区域(西海岸、中部、东海岸)。然而，城市永远是最低的节点。我需要一些可以独立于层次结构而存在的东西。

更新我原来的问题我使用了一个简单的例子。在我的实际解决方案中，我必须利用多个联接来获取我需要的层次结构。我正在处理以下查询，但截至目前它为我希望填充的每一列返回 null。很可能是案例陈述有问题？

;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

试试这个...使用前请使用更多样本数据进行测试。

表格脚本和示例数据

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

查询

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) 

输出

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

在线演示:http://www.sqlfiddle.com/#!18/7470b/3/0