October 16, 2009 at 2:16 pm
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
October 22, 2009 at 10:45 pm
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