将分层数据转换为平面表 [英] Transform hierarchical data into flat table
本文介绍了将分层数据转换为平面表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在使用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>
这篇关于将分层数据转换为平面表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
