Solved

# Get Dept Level of Hierarchy of Given ItemCode.

Posted on 2009-05-02
Medium Priority
313 Views
Hi,

ItemCode   ItemName   ItemGroup
SFD             Softdrink
CCL            Coca Cola    SFD
FNT             Fanta           SFD
SPR             Sprite          SFD
SPRZ             Sprite   Zero       SPR
ACL             Alcohol
TQL             Tequila        ACL
VDK             Vodka         ACL

I want to know a depth level for given ItemCode?  Given SPRZ get 3.

How could I do it?

Thank you.
0
Question by:emi_sastra
• 3
• 2

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 1000 total points
ID: 24286136
in simple terms: with sql 2005 , you simply run a recursive query to find it, for example:
this might be the most effective if you need to know other information as well

an alternative, you could create a recursive function like this:
``````create function dbo.GetDepth(@ItemCode   varchar(20))
returns int
as
begin
declare @res int
declare @parent_group varchar(20)
select @parent_group = ItemGroup from yourtable where itemcode = @itemcode
set @res = @@rowcount
if @res > 0
set @res = @res    dbo.GetDepth(@parent_group)
return @res
end
``````
0

LVL 41

Assisted Solution

Sharath earned 1000 total points
ID: 24287195
try this
``````DECLARE @tableA table (itemCode NVARCHAR(3), itemName NVARCHAR(100), itemGroup NVARCHAR(3))
DECLARE @itemCode varchar(10)
SET @itemCode = 'SPZ'
INSERT INTO @tableA
SELECT 'SFD', 'Softdrink', NULL
UNION ALL SELECT 'CCL', 'Coca Cola', 'SFD'
UNION ALL SELECT 'FNT', 'Fanta', 'SFD'
UNION ALL SELECT 'SPR', 'Sprite', 'SFD'
UNION ALL SELECT 'ACL', 'Alcohol', NULL
UNION ALL SELECT 'TQL', 'Tequila', 'ACL'
UNION ALL SELECT 'VDK', 'Vodka', 'ACL'
UNION ALL SELECT 'SMN', 'Smirnoff', 'VDK'
UNION ALL SELECT 'SPZ', 'Sprite Zero', 'SPR'

-- recursion using common table expression
;WITH cte AS (
SELECT itemCode, itemName, itemGroup
, 1 AS itemDepth
FROM @tableA -- replace with your table name
WHERE itemCode = @itemCode
UNION ALL
SELECT t.itemCode, t.itemName, t.itemGroup
, c.itemDepth + 1 AS itemDepth
FROM @tableA t
INNER JOIN cte c ON c.itemGroup = t.itemCode)
SELECT max(itemDepth) FROM cte
``````
0

LVL 1

Author Comment

ID: 24288456
Hi AngelIII,

I am not familiar with SP, would you please add sample data to it and show result at the SP?

Thank you.
0

LVL 1

Author Comment

ID: 24288458
Hi Sharat_123,

It works.

Thank you very much for your help.
0

LVL 143

Expert Comment

ID: 24288913
once you created my function, you use it like this with your actual table
``````select t.*, dbo.GetDepth(t.ItemCode) depth
from yourtable t
``````
0

LVL 1

Author Comment

ID: 24289936
Hi AngelIII,

Ok.

Thank you very much for your help.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
###### Suggested Courses
Course of the Month16 days, 9 hours left to enroll