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]
1 Jewelry by Catalog
2 Jewelry by Style
3 Jewelry by Color
4 Jewelry by Size
5 Necklace Jewelry by Style
5 .Necklace Jewelry by Style
6 Long Necklace Jewelry by Style
6 Long Necklace Jewelry by Size
6 Long Necklace Red Jewelry by Color
6 Long Necklace Fall 2009 Jewelry by Catalog
6 Long Necklace Spring 2010 Jewelry by Catalog
6 .Long Necklace Jewelry by Style
6 .Long Necklace Jewelry by Size
6 .Long Necklace Red Jewelry by Color
6 .Long Necklace Fall 2009 Jewelry by Catalog
6 .Long Necklace Spring 2010 Jewelry by Catalog
6 ..Long Necklace Red
6 ..Long Necklace Fall 2009
6 ..Long Necklace Spring 2010 Jewelry 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