Get Dept Level of Hierarchy of Given ItemCode.

Posted on 2009-05-02
Last Modified: 2012-05-06

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.
Question by:emi_sastra
    LVL 142

    Accepted Solution

    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
      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

    Open in new window

    LVL 40

    Assisted Solution

    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 '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
    	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

    Open in new window

    LVL 1

    Author Comment

    Hi AngelIII,

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

    Thank you.
    LVL 1

    Author Comment

    Hi Sharat_123,

    It works.

    Thank you very much for your help.
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    once you created my function, you use it like this with your actual table
    select t.*, dbo.GetDepth(t.ItemCode) depth
      from yourtable t

    Open in new window

    LVL 1

    Author Comment

    Hi AngelIII,


    Thank you very much for your help.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    Title # Comments Views Activity
    SSN Format in Oracle 2 35
    Number Format 1 29
    Calculate age in Access report 11 22
    SQL Select Query problems 10 36
    In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now