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.

Did you try my answer? I just did it and your second example returns exactly the way you posted it... The problem in your case is two folded: 1) dynamically named columns and 2) hierarchy. Both examples are straight forward three levels deep. Is this fix? — Shnugo, Dec 27 '16 at 12:12
Thank you a lot, u hit the target sir [dynamically named columns and hiearchy] the problem is that the level of hierarchy is not fixed (but in 99% of cases 3) — Ismail, Dec 27 '16 at 12:40
Ismail, the results you posted are not compatible with the data you posted. For me there is no chance to see, how your hierarchy works without a real example. — Shnugo, Dec 27 '16 at 12:40
Please recheck the post I updated it with the real data I am using — Ismail, Dec 27 '16 at 12:49
Ismail, you neglected to provide the expected output fitting to your *real data*. Your section *Result using Shnugo's answer* does not comply to the data given... I have no idea what you really need. — Shnugo, Dec 27 '16 at 12:52
Hi Ismail, fixing your question is a good idea :-) In the meanwhile I updated my answer once again, please check UPDATE 2 — Shnugo, Dec 27 '16 at 13:00

Shnugo · Accepted Answer · 2016-12-27T12:59:42.327

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>

Thanks a lot you have provided me with great help and even give answear better than what I was expecting. — Ismail, Dec 27 '16 at 13:11
I am in the phase of testing with this hierarchical table design thats why the question was messy, can you please guide me to better design what I need [Generalisation for work orders] — Ismail, Dec 27 '16 at 13:13
@IsmailKarchi please start a new question with a link to this and place a link here below my answer to alert me. A better design is - quite sure possible, as *Key-Value-Pairs* in conncetion with *self-referenced-hierarchy* seems to be over-complicated... But I do not know enough about your needs. Start a new question with more details about your actual situation (row count, use-case(s), count of groups, data integrity... and - the main question! - WHY?) — Shnugo, Dec 27 '16 at 13:23

score 0 · Answer 2 · answered Dec 27 '16 at 11:11

0

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;

answered Dec 27 '16 at 11:11

Gurwinder Singh

38,557
6
51
76

Thanks, it solved this specific case only, what if I have multiple values. Let me give an example – Ismail Dec 27 '16 at 11:24
Please post it in the question – Gurwinder Singh Dec 27 '16 at 11:27

Transform hierarchical data into flat table

2 Answers2

UPDATE

UPDATE 2: Using your real data

Linked