January 31, 2017 at 11:55 am
Need help on a query getting hierachy data.
I have the following table and data arranged as below.
declare @tblShop as table
(
ItemName varchar(20),
HLabel varchar(10),
ItemId int
)
insert into @tblShop values ('Bike', '1.',0)
insert into @tblShop values ('Mountain', '1.1',11)
insert into @tblShop values ('Racer', '1.2',12)
insert into @tblShop values ('Plates', '2.',0)
insert into @tblShop values ('Ceramic', '2.1',21)
insert into @tblShop values ('Porcelein', '2.2',22)
insert into @tblShop values ('Foam', '2.3',23)
My desired results are
Bike - Mountain 1.1 11
Bike - Racer 1.2 12
Plates - Ceramic 2.1 21
Plates - Porcelein 2.2 22
Plates - foam 2.3 23
January 31, 2017 at 1:56 pm
Does your hierarchy ever go more than 1 level? Your sample doesn't suggest so, so I haven't done more:WITH CTE AS(
SELECT *,
LEFT(TS.HLabel, CHARINDEX('.', TS.Hlabel,0)) AS Parent
FROM @tblShop TS
WHERE TS.ItemId != 0)
SELECT TS.ItemName + ' - ' + CTE.ItemName AS Item,
CTE.HLabel,
CTE.ItemId
FROM @tblShop TS
JOIN CTE ON TS.HLabel = CTE.Parent
WHERE TS.ItemId = 0;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 31, 2017 at 2:23 pm
I think that this will perform better, because LEFT is not SARGable, but LIKE can be SARGable when there is no leading wildcard as here.
SELECT p.ItemName + ' - ' + c.ItemName, c.HLabel, c.ItemId
FROM @tblShop p
INNER JOIN @tblShop c
ON c.HLabel LIKE p.HLabel + '[0-9]%'
AND c.HLabel NOT LIKE p.HLabel + '%.%'
I've also included a line to prevent a node from matching a grandchild node (assuming that each generation is separated by a decimal point, e.g., 1.3.5).
Your main problem is that you don't really have a hierarchy here. Hierarchies are usually depicted with an adjacency list. Nested sets is another option, but they are harder for humans to read and maintain. This can be seen if you consider that with the matching that I have done, an item with HLabel 1.1 will be considered a parent of HLabel 1.11, 1.12, etc. I haven't investigated whether ThomA's code has a similar flaw.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 31, 2017 at 3:03 pm
drew.allen - Tuesday, January 31, 2017 2:23 PMYour main problem is that you don't really have a hierarchy here. Hierarchies are usually depicted with an adjacency list. Nested sets is another option, but they are harder for humans to read and maintain. This can be seen if you consider that with the matching that I have done, an item with HLabel 1.1 will be considered a parent of HLabel 1.11, 1.12, etc. I haven't investigated whether ThomA's code has a similar flaw.
Had a quick test, no, mine does not. However, mine would likely not give the desired result if you had a layer at 1.1.1.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply