-1

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?

2

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
  • Thanks for this, may I ask why the dataset doesn't match? Is it the ParentId? I was thinking that can be extrapolated by the relationship (being to the right of the parent level) – sojim2 Nov 5 at 17:16
  • Your Table B should be 14 rows deep if it's a full representation of Table A. The most unclear was around the ID for Table B, I had to guess at what it's meaning was as it's not part of Table A (that turns into ParentID in Table B). – Jonathan Fite Nov 6 at 12:52
-1
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.