SQL Server: How to unpivot from pivoted table back to a self referencing table

Question

How does one unpivot from a pivoted table such as this?

Table A:

+----+------------+--------+--------+--------+
| Id | Level0     | Level1 | Level2 | Level3 |
+----+------------+--------+--------+--------+
| 0  | TMI        |        |        |        |
+----+------------+--------+--------+--------+
| 1  | TMI        | A      |        |        |
+----+------------+--------+--------+--------+
| 2  | TMI        | A      | B      |        |
+----+------------+--------+--------+--------+
| 3  | TMI        | A      | B      | C      |
+----+------------+--------+--------+--------+
| 4  | TMI        | A      | B      | D      |
+----+------------+--------+--------+--------+

Back to self referencing table like this:

Table B:

+----+-----------+----------+--------+
| Id | LevelName | ParentId | Level  |
+----+-----------+----------+--------+
| 0  | TMI       |          | Level0 |
+----+-----------+----------+--------+
| 1  | A         | 0        | Level1 |
+----+-----------+----------+--------+
| 2  | B         | 1        | Level2 |
+----+-----------+----------+--------+
| 3  | C         | 2        | Level3 |
+----+-----------+----------+--------+
| 4  | D         | 2        | Level3 |
+----+-----------+----------+--------+

I can go from Table B to Table A, but how to go from Table A to Table B?

Answer 1

This example appears to work, although your two datasets don't match. But this should at least get you close.

/** Build up a sample dataset to work with.
    **/

DECLARE @Table TABLE
    (
    ID INT NOT NULL
    , Level0 VARCHAR(10) NULL
    , Level1 VARCHAR(10) NULL
    , Level2 VARCHAR(10) NULL
    , Level3 VARCHAR(10) NULL
    )

INSERT INTO @Table 
(ID, Level0, Level1, Level2, Level3)
VALUES (0, 'TMI', NULL, NULL, NULL)
    , (1, 'TMI', 'A', NULL, NULL)
    , (2, 'TMI', 'A', 'B', NULL)
    , (3, 'TMI', 'A', 'B', 'C')
    , (4, 'TMI', 'A', 'B', 'D')

/** First, we unpivot the data in CTE_UnPvt
    -   Getting the LevelName and Level fields.
    -   Note that if you have more `Level#` then you need to add them here.
        Making this dynamic is outside this example, but there are plenty of 
        examples for making pivot/unpivot dynamic out there.

    Second, add the ID field back (which appares to just be an arbitrary incrementing
    ID in your example, so I've done that using ROW_NUMBER()

    **/

;WITH CTE_UnPvt AS
    (
    SELECT ID AS ParentID
        , LevelName
        , [Level]
    FROM @Table AS T
        UNPIVOT ([Level] FOR LevelName IN (Level0, Level1, Level2, Level3)) AS unpvt 
    )
, CTE_AddID AS
    (
    SELECT ID = ROW_NUMBER() OVER (ORDER BY ParentID, LevelName)
        , LevelName 
        , ParentID 
        , [Level]
    FROM CTE_UnPvt AS P
    )
SELECT ID
    , LevelName
    , ParentID 
    , [Level]
FROM CTE_AddID

Answer 2

SELECT level0,   NULL, 'Level0' FROM tableA
UNION 
SELECT level1, level0, 'Level1' FROM tableA
UNION 
SELECT level2, level1, 'Level2' FROM tableA
UNION 
SELECT level3, level2, 'Level3' FROM tableA