Преобразуйте иерархические данные в плоскую таблицу
Я исследую иерархические данные (как решение для обобщения рабочих заданий) с помощью MS SQL.
*
Я открыт для изменения дизайна моей исходной таблицы и / или для добавления других таблиц.
*
Вот мои данные
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
[Результат с использованием ответа Гурвиндера]
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
[Подход Шунго]
<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>
Большое спасибо за ваше время, ребята.
2 ответа
Решение
Как насчет этого трюка?
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) можно найти в общем - но вам нужно предоставить более подробную информацию...
ОБНОВИТЬ
Следующее не будет использовать заданную глубину в качестве фильтра, но запрос для проверки, является ли узел листовым узлом
Я добавил еще одну строку в конце
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: Использование ваших реальных данных
Как вы уже узнали, ваш вопрос был довольно беспорядочным... Не знаю, что вам действительно нужно, но если я провожу ваши реальные данные через этот запрос, я получу следующее:
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>
select t1.value num,
t2.value sector,
t3.value product,
t4.value quantity
from table t1
inner join table t2
on t1.id = t2.parentid
and t1.parentid = 0
inner join table t3
on t2.id = t3.parentid
inner join table t4
on t3.id = t4.parentid;