将分层数据转换为平面表

本文介绍了将分层数据转换为平面表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在使用ms sql研究分层数据(作为通用化工作订单的解决方案).

I am researching about hierarchical data (as a solution to generalize work orders) by using ms sql.

*

我愿意在初始表上进行设计更改和/或添加其他内容 桌子.

I am open to design change on my initial table and/or to add other tables.

*

这是我的数据

ID ParentID Type Value 38 0 Num 327 39 38 Sector 21 40 38 Sector 22 43 40 Product NS 44 40 Product MS 50 40 Temp RAS 48 44 Quantity 60 47 43 Quantity 25 41 39 Product ARF 42 39 Product BRF 49 39 Temp RAS 51 39 Cible Acarien A. 46 42 Quantity 30 52 42 Cible Acarien B. 45 41 Quantity 20

我想将其转换为:

Num Sector Product Quantity 327 21 ARF 20 327 21 BRF 30 327 22 NS 25 327 22 MS 60

[使用Gurwinder的answear的结果]

[Result using Gurwinder's answear]

num sector product quantity 327 22 MS 60 327 22 NS 25 327 21 BRF 30 327 21 BRF Acarien B. 327 21 ARF 20

[Shungo的方法]

[Shungo's approach]

<root> <row Num="327" Sector="s2" Temp="normal" /> <row Num="327" Sector="s2" Product="BRF" Qte="70" /> <row Num="327" Sector="s2" Product="ARF" Qte="45" /> <row Num="327" Sector="s1" Temp="normal" /> <row Num="327" Sector="s1" Cible="Acarien a." /> <row Num="327" Sector="s1" Product="NS" Qte="35" /> <row Num="327" Sector="s1" Product="NS" Cible="Acarien b." /> <row Num="327" Sector="s1" Product="MS" Qte="60" /> </root>

非常感谢您的陪伴.

Thank's a lot for your time guys.

推荐答案

这个技巧如何?

DECLARE @tbl TABLE(ID INT,ParentID INT,Type VARCHAR(10),Value VARCHAR(10)) INSERT INTO @tbl VALUES (1,0,'Num','327') ,(2,1,'Sector','21') ,(3,1,'Sector','22') ,(4,2,'Product','ARF') ,(5,2,'Product','BRF') ,(6,3,'Product','NS') ,(7,3,'Product','MS') ,(8,4,'Quantity','20') ,(9,5,'Quantity','30') ,(10,6,'Quantity','25') ,(11,7,'Quantity','60'); WITH recCTE AS ( SELECT *,0 AS HLevel,Type + N'="' + CAST(Value AS NVARCHAR(MAX)) + N'" ' AS attr FROM @tbl WHERE ParentID=0 UNION ALL SELECT t.*,r.HLevel+1,attr+t.Type + N'="' + CAST(t.Value AS NVARCHAR(MAX)) + N'" ' FROM @tbl AS t INNER JOIN recCTE AS r ON t.ParentID=r.ID ) SELECT CAST(N'<row ' + attr + N'/>' AS XML) FROM recCTE WHERE HLevel=3 FOR XML PATH(''),ROOT('root')

结果

<root> <row Num="327" Sector="22" Product="MS" Quantity="60" /> <row Num="327" Sector="22" Product="NS" Quantity="25" /> <row Num="327" Sector="21" Product="BRF" Quantity="30" /> <row Num="327" Sector="21" Product="ARF" Quantity="20" /> </root>

该XML易于查询... 最深的级别(在这里我取HLevel = 3)可以找到-但是您需要提供更多详细信息...

This XML is easy to query... The deepest Level (here I took HLevel=3) can be found generically - but you'd need to provide more details...

以下内容将不使用给定深度作为过滤器，而是使用查询来检查节点是否为叶节点

The following will not use a given depth as filter but a query to check, if a node is a leaf-node

我在末尾又添加了一行

DECLARE @tbl TABLE(ID INT,ParentID INT,Type VARCHAR(100),Value VARCHAR(100)) INSERT INTO @tbl VALUES (1,0,'Num','327') ,(2,1,'Sector','21') ,(3,1,'Sector','22') ,(4,2,'Product','ARF') ,(5,2,'Product','BRF') ,(6,3,'Product','NS') ,(7,3,'Product','MS') ,(8,4,'Quantity','20') ,(9,5,'Quantity','30') ,(10,6,'Quantity','25') ,(11,7,'Quantity','60') ,(13,11,'SomeMore','Test as fourth'); WITH recCTE AS ( SELECT t.* ,0 AS HLevel ,t.Type + N'="' + CAST(t.Value AS NVARCHAR(MAX)) + N'" ' AS attr ,CASE WHEN EXISTS(SELECT 1 FROM @tbl AS x WHERE x.ParentID=t.ID) THEN 0 ELSE 1 END AS IsLeaf FROM @tbl AS t WHERE ParentID=0 UNION ALL SELECT t.* ,r.HLevel+1 ,attr+t.Type + N'="' + CAST(t.Value AS NVARCHAR(MAX)) + N'" ' ,CASE WHEN EXISTS(SELECT 1 FROM @tbl AS x WHERE x.ParentID=t.ID) THEN 0 ELSE 1 END AS IsLeaf FROM @tbl AS t INNER JOIN recCTE AS r ON t.ParentID=r.ID ) SELECT CAST(N'<row ' + attr + N'/>' AS XML) FROM recCTE WHERE IsLeaf=1 FOR XML PATH(''),ROOT('root')

结果

<root> <row Num="327" Sector="22" Product="MS" Quantity="60" SomeMore="Test as fourth" /> <row Num="327" Sector="22" Product="NS" Quantity="25" /> <row Num="327" Sector="21" Product="BRF" Quantity="30" /> <row Num="327" Sector="21" Product="ARF" Quantity="20" /> </root>

更新2:使用您的真实数据

正如您已经发现的那样，您的问题相当混乱……不知道您真正需要什么，但是如果我通过此查询运行您的真实数据，我会得到:

UPDATE 2: Using your real data

As you've found out alread, your question was quite a mess... Don't know what you really need, but if I run your real data through this query I'd get this:

DECLARE @tbl TABLE(ID INT,ParentID INT,Type VARCHAR(100),Value VARCHAR(100)) INSERT INTO @tbl VALUES (38,0,'Num','327') ,(39,38,'Sector','21') ,(40,38,'Sector','22') ,(43,40,'Product','NS') ,(44,40,'Product','MS') ,(50,40,'Temp','RAS') ,(48,44,'Quantity','60') ,(47,43,'Quantity','25') ,(41,39,'Product','ARF') ,(42,39,'Product','BRF') ,(49,39,'Temp','RAS') ,(51,39,'Cible','Acarien A.') ,(46,42,'Quantity','30') ,(52,42,'Cible','Acarien B.') ,(45,41,'Quantity','20'); WITH recCTE AS ( SELECT t.* ,0 AS HLevel ,t.Type + N'="' + CAST(t.Value AS NVARCHAR(MAX)) + N'" ' AS attr ,CASE WHEN EXISTS(SELECT 1 FROM @tbl AS x WHERE x.ParentID=t.ID) THEN 0 ELSE 1 END AS IsLeaf FROM @tbl AS t WHERE ParentID=0 UNION ALL SELECT t.* ,r.HLevel+1 ,attr+t.Type + N'="' + CAST(t.Value AS NVARCHAR(MAX)) + N'" ' ,CASE WHEN EXISTS(SELECT 1 FROM @tbl AS x WHERE x.ParentID=t.ID) THEN 0 ELSE 1 END AS IsLeaf FROM @tbl AS t INNER JOIN recCTE AS r ON t.ParentID=r.ID ) SELECT CAST(N'<row ' + attr + N'/>' AS XML) FROM recCTE WHERE IsLeaf=1 FOR XML PATH(''),ROOT('root')

结果

<root> <row Num="327" Sector="22" Temp="RAS" /> <row Num="327" Sector="22" Product="MS" Quantity="60" /> <row Num="327" Sector="22" Product="NS" Quantity="25" /> <row Num="327" Sector="21" Temp="RAS" /> <row Num="327" Sector="21" Cible="Acarien A." /> <row Num="327" Sector="21" Product="BRF" Quantity="30" /> <row Num="327" Sector="21" Product="BRF" Cible="Acarien B." /> <row Num="327" Sector="21" Product="ARF" Quantity="20" /> </root>