Transform hierarchical data into flat table

Question

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.

*

Here is my data

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

I would like to transform it into :

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

[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'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.

Answer 1

What about this trick?

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

The result

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

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...

UPDATE

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

I added one more row at the end

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

The result

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

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

The result

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

Answer 2

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;