Looking for results of a Tree Output in Pivot Table format

  • Looking to convert/pivot the result of the query below into the table format that resembles the same result with all the possible categories and subcategories listed. The level may be unknown so the Category could go beyond the Parent, GrandParent, GreatGrandParent relationship.

    Requested Sample Result

    [CategoryID][Name][ParentCategoryName][GrandParentCategory][GreatGrandParentCategory]

    1Jewelry by Catalog

    2Jewelry by Style

    3Jewelry by Color

    4Jewelry by Size

    5NecklaceJewelry by Style

    5.NecklaceJewelry by Style

    6Long NecklaceJewelry by Style

    6Long NecklaceJewelry by Size

    6Long NecklaceRedJewelry by Color

    6Long NecklaceFall 2009Jewelry by Catalog

    6Long NecklaceSpring 2010Jewelry by Catalog

    6.Long NecklaceJewelry by Style

    6.Long NecklaceJewelry by Size

    6.Long NecklaceRedJewelry by Color

    6.Long NecklaceFall 2009Jewelry by Catalog

    6.Long NecklaceSpring 2010Jewelry by Catalog

    6..Long NecklaceRed

    6..Long NecklaceFall 2009

    6..Long NecklaceSpring 2010Jewelry by Catalog


    --Create Sample Data

    CREATE TABLE #tblCategory (

    CategoryID int

    , Name varchar(100)

    , ParentCategoryID int

    )

    INSERT INTO #tblCategory VALUES (1,'Jewelry by Catalog',0)

    INSERT INTO #tblCategory VALUES (2,'Jewelry by Style',0)

    INSERT INTO #tblCategory VALUES (3,'Jewelry by Color',0)

    INSERT INTO #tblCategory VALUES (4,'Jewelry by Size',0)

    INSERT INTO #tblCategory VALUES (5,'Necklace',2)

    INSERT INTO #tblCategory VALUES (6,'Long Necklace',2)

    INSERT INTO #tblCategory VALUES (6,'Long Necklace',4)

    INSERT INTO #tblCategory VALUES (7,'Purple',3)

    INSERT INTO #tblCategory VALUES (8,'Red',3)

    INSERT INTO #tblCategory VALUES (9,'Green',3)

    INSERT INTO #tblCategory VALUES (10,'Fall 2009',1)

    INSERT INTO #tblCategory VALUES (11,'Spring 2010',1)

    INSERT INTO #tblCategory VALUES (6,'Long Necklace',10)

    INSERT INTO #tblCategory VALUES (6,'Long Necklace',11)

    INSERT INTO #tblCategory VALUES (6,'Long Necklace',8)

    GO

    -- Query Result

    ; WITH cte AS (

    SELECT CategoryID

    , Name

    , ParentCategoryID

    , 0 As [level]

    FROM #tblCategory

    UNION ALL

    SELECT child.CategoryID

    , child.Name

    , child.ParentCategoryID

    , parent.level + 1

    FROM cte parent

    INNER JOIN #tblCategory AS child WITH (NOLOCK)

    ON parent.CategoryID = child.ParentCategoryID

    )

    SELECT *

    FROM cte

    ORDER BY [level], ParentCategoryID, CategoryID

    GO

  • Hi there...

    You were almost there... I just fixed your WITH (NOLOCK) issue and concatenated the names...

    (I also cahnged it from temp table to table variable, for readers convenience)

    DECLARE @tblCategory TABLE(

    CategoryID int

    , Name varchar(100)

    , ParentCategoryID int

    )

    INSERT INTO @tblCategory VALUES (1,'Jewelry by Catalog',0)

    INSERT INTO @tblCategory VALUES (2,'Jewelry by Style',0)

    INSERT INTO @tblCategory VALUES (3,'Jewelry by Color',0)

    INSERT INTO @tblCategory VALUES (4,'Jewelry by Size',0)

    INSERT INTO @tblCategory VALUES (5,'Necklace',2)

    INSERT INTO @tblCategory VALUES (6,'Long Necklace',2)

    INSERT INTO @tblCategory VALUES (6,'Long Necklace',4)

    INSERT INTO @tblCategory VALUES (7,'Purple',3)

    INSERT INTO @tblCategory VALUES (8,'Red',3)

    INSERT INTO @tblCategory VALUES (9,'Green',3)

    INSERT INTO @tblCategory VALUES (10,'Fall 2009',1)

    INSERT INTO @tblCategory VALUES (11,'Spring 2010',1)

    INSERT INTO @tblCategory VALUES (6,'Long Necklace',10)

    INSERT INTO @tblCategory VALUES (6,'Long Necklace',11)

    INSERT INTO @tblCategory VALUES (6,'Long Necklace',8)

    -- Query Result

    ;WITH cte AS (

    SELECT CategoryID

    , CAST(Name as NVARCHAR(MAX)) as Name

    , ParentCategoryID

    , 0 As [level]

    FROM @tblCategory

    UNION ALL

    SELECT child.CategoryID

    , child.Name + '-->' + ISNULL(parent.Name,'')

    , child.ParentCategoryID

    , parent.level + 1

    FROM cte parent

    INNER JOIN @tblCategory AS child

    ON parent.CategoryID = child.ParentCategoryID

    )

    SELECT *

    FROM cte

    ORDER BY [level], ParentCategoryID, CategoryID

    GO

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply