Reverse query for Hierarchical Data

I have seen one article

Managing Hierarchical Data in MySQL

  • How to search text in all database objects in MS SQL Server
  • sp_executesql or execute without variables
  • Issue creating a SSAS Dimension that takes a general ledger period and maps it to a date value
  • Performance of large EAV/open schema systems on SQL Server
  • How would you find the 'GOOD' ID when cancellation is involved?
  • SQL Server - Using SQL JOIN and UNION

  • That use below data from category table

      category_id | name                 | parent |
    +-------------+----------------------+--------+
    |           1 | ELECTRONICS          |   NULL |
    |           2 | TELEVISIONS          |      1 |
    |           3 | TUBE                 |      2 |
    |           4 | LCD                  |      2 |
    |           5 | PLASMA               |      2 |
    |           6 | PORTABLE ELECTRONICS |      1 |
    |           7 | MP3 PLAYERS          |      6 |
    |           8 | FLASH                |      7 |
    |           9 | CD PLAYERS           |      6 |
    |          10 | 2 WAY RADIOS         |      6 |
    

    In the below query and returns below data.

      SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
     FROM category AS t1
     LEFT JOIN category AS t2 ON t2.parent = t1.category_id
     LEFT JOIN category AS t3 ON t3.parent = t2.category_id
     LEFT JOIN category AS t4 ON t4.parent = t3.category_id
     WHERE t1.name = 'ELECTRONICS';
    
     +-------------+----------------------+--------------+-------+
     | lev1        | lev2                 | lev3         | lev4  |
     +-------------+----------------------+--------------+-------+
     | ELECTRONICS | TELEVISIONS          | TUBE         | NULL  |
     | ELECTRONICS | TELEVISIONS          | LCD          | NULL  |
     | ELECTRONICS | TELEVISIONS          | PLASMA       | NULL  |
     | ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |
     | ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS   | NULL  |
     | ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |
     +-------------+----------------------+--------------+-------+
    6 rows in set (0.00 sec)
    

    Now my question is if I have a table or query that has below data

      +-------------+----------------------+--------------+-------+
      | lev1        | lev2                 | lev3         | lev4  |
      +-------------+----------------------+--------------+-------+
      | ELECTRONICS | TELEVISIONS          | TUBE         | NULL  |
      | ELECTRONICS | TELEVISIONS          | LCD          | NULL  |
      | ELECTRONICS | TELEVISIONS          | PLASMA       | NULL  |
      | ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |
      | ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS   | NULL  |
      | ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |
      +-------------+----------------------+--------------+-------+
    

    How I can make a Reverse query that returns below data:

       category_id | name                 | parent |
       +-------------+----------------------+--------+
     |           1 | ELECTRONICS          |   NULL |
     |           2 | TELEVISIONS          |      1 |
     |           3 | TUBE                 |      2 |
     |           4 | LCD                  |      2 |
     |           5 | PLASMA               |      2 |
     |           6 | PORTABLE ELECTRONICS |      1 |
     |           7 | MP3 PLAYERS          |      6 |
     |           8 | FLASH                |      7 |
     |           9 | CD PLAYERS           |      6 |
     |          10 | 2 WAY RADIOS         |      6 |
    

    If I could have also value of level field (0 or 1 or…) in this reverse query was very good.

       category_id | name                 | parent |position
    
       +-------------+----------------------+--------+-------
     |           1 | ELECTRONICS          |   NULL |0
     |           2 | TELEVISIONS          |      1 |0
     |           3 | TUBE                 |      2 |3
     |           4 | LCD                  |      2 |1
     |           5 | PLASMA               |      2 |2
    

    One Solution collect form web for “Reverse query for Hierarchical Data”

    Where do the IDs come from? Suppose, you have another table with IDs per item, then the code could look similar to this:

    select i.ID, l.lev1 as Name, NULL as Parent
    from IDTable i 
         join LevelTable l on i.Name = l.lev1
    union
    select i.ID, l.lev2 as Name, (select j.ID from IDTable j where j.Name = l.lev1)
    from IDTable i 
         join LevelTable l on i.Name = l.lev2
    union
    select i.ID, l.lev3 as Name, (select j.ID from IDTable j where j.Name = l.lev2)
    from IDTable i 
         join LevelTable l on i.Name = l.lev3
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.