Hierarchy design to have equal levels in each tree.

  • Hi All,

    I have a requirement, where i need to have a equal levels in each hierarchy. I am trying to use the recursive CTE's but could not get the exact logic.

    I have a table as below:

    product_cdproduct_descproduct_parent_cdproduct_id

    TOTPRODTotal Commercial BeveragesNULL1

    BT01Non-Alcoholic Ready-To-Drink BeveragesTOTPROD2

    BC02Packaged Water, RTDBT0117

    BS991Packaged Water Plain, RTDBC0260

    BS999Packaged WaterEnhanced, RTDBC0219

    BS998Packaged Water Bulk, RTDBS99118

    I want the below sample output to make the hierarchy equal as above.

    BS010Packaged Water Pouch, RTDBS99150

    BC01BeerBT0233

    BT02Alcoholic BeveragesTOTPROD4

    BC01d1BeerBC0133

    BC01d1d1BeerBC01d133

    Can any one help me on this?

  • First, hierarchies are not inherently balanced. Trying to force them to be balanced is like trying to force a square peg in a round hole. What is the business case for having it be balanced?

    Second, if you've already tried something, share it with us. It's often much easier to troubleshoot existing code than to create new code from scratch.

    Also, it really helps if you follow Forum Etiquette: How to post data/code on a forum to get the best help.[/url]

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Hi Drew,

    Thanks for your response. For your query on balancing of hierarchy-- we are displaying this data in a cube. For example max level hierarchy is 4. Few of the products we dont have all the levels in that case business wants the duplicated hierarachy in next levels.

    Below is the code for Test data.

    CREATE TABLE [mart].[t_dim_product_temp](

    [product_cd] [varchar](10) NOT NULL PRIMARY KEY CLUSTERED,

    [product_desc] [varchar](100) NOT NULL,

    [product_parent_cd] [varchar](10) NOT NULL,

    [product_id] [int] NOT NULL)

    --------------------------------------------

    INSERT INTO [mart].[t_dim_product_temp]

    (product_cd,product_desc,product_parent_cd,product_id)

    SELECT 'TOTPROD','Total Commercial Beverages','',1 UNION ALL

    SELECT 'BT01','Non-Alcoholic Ready-To-Drink Beverages','TOTPROD',2 UNION ALL

    SELECT 'BC02','Packaged WaterRTD','BT01',17 UNION ALL

    SELECT 'BS991','Packaged Water PlainRTD','BC02',60 UNION ALL

    SELECT 'BS999','Packaged WaterEnhancedRTD','BC02',19 UNION ALL

    SELECT 'BS998','Packaged Water BulkRTD','BS991',18 UNION ALL

    SELECT 'BS010','Packaged Water PouchRTD','BS991',50 UNION ALL

    SELECT 'BC01','Beer','BT02',33 UNION ALL

    SELECT 'BT02','Alcoholic Beverages','TOTPROD',4

    ------------------------------

    Below is the code snippet, which i tried.

    create table #Product_temp (product_cd varchar(10) not null,

    product_desc varchar(100) not null,

    product_parent_cd varchar(10) not null,

    ready_to_drink_ind varchar(1) not null,

    ready_to_drink_ind_desc varchar(40) not null,

    Product_id int not null,

    level int)

    ;WITH w1 ( product_cd, product_desc,product_parent_cd, ready_to_drink_ind,ready_to_drink_ind_desc,Product_id, level ) AS

    (SELECT

    product_cd,

    product_desc,

    product_parent_cd,

    ready_to_drink_ind,

    ready_to_drink_ind_desc,

    Product_id,

    1 AS level

    FROM

    mart.t_dim_product

    WHERE

    product_parent_cd = 'TOTPROD'

    UNION ALL

    SELECT

    mart.t_dim_product.product_cd,

    mart.t_dim_product.product_desc,

    mart.t_dim_product.product_parent_cd,

    mart.t_dim_product.ready_to_drink_ind,

    mart.t_dim_product.ready_to_drink_IND_DESC,

    mart.t_dim_product.product_id,

    level + 1

    FROM

    mart.T_dim_product JOIN w1 ON mart.t_dim_product.product_parent_cd = w1.product_cd )

    SELECT * FROM w1

    INSERT into #Product_temp

    (product_cd,

    product_desc,

    product_parent_cd,

    ready_to_drink_ind,

    ready_to_drink_ind_desc,

    Product_id,

    level )

    ( SELECT

    product_cd,

    product_desc,

    product_parent_cd,

    ready_to_drink_ind,

    ready_to_drink_ind_desc,

    Product_id,

    level

    from w1 )

    select @maxLevel = max(level) from #Product_temp

    -- select @level = level from #Product_temp

    print @maxLevel

    DECLARE @Iterator INT

    SET @Iterator = 0

    WHILE (@Iterator < @maxLevel)

    BEGIN

    select @level = level from #Product_temp

    insert into #Product_temp (product_cd, product_desc, product_parent_cd, ready_to_drink_ind, ready_to_drink_ind_desc, Product_id,level)

    ( SELECT p1.product_cd + 'd1', p1.product_desc, p1.product_parent_cd, p1.ready_to_drink_ind, p1.ready_to_drink_ind_desc, p1.Product_id, null from #Product_temp p1

    LEFT OUTER JOIN #Product_temp p2 on p1.PRODUCT_CD = p2.PRODUCT_PARENT_CD

    and P1.level = p2.level

    WHERE p1.level = @level

    )

    Set @Iterator = @Iterator + 1

    END

    GO

    In the above query the logic is not correct and hence it is giving incorrect results.

    I would like to have the Output as below for the above table

    product_cd, prod_desc, prod_parent_cd, prod_id

    BC01d1 Beer BC01 33

    BC01d1d1 Beer BC01d1 33

    Kindly help me on this.

  • Should the script extend all "short" branches to the maximum number of levels? Including BS999 in your example?

    Them the idea is get leafs only, get their levels, get max level and them expand those which level is <maxlevel using tally which has number of rows equal to maxlevel.

  • If i got it right try this

    CREATE TABLE [t_dim_product_temp](

    [product_cd] [varchar](100) NOT NULL PRIMARY KEY CLUSTERED,

    [product_desc] [varchar](100) NOT NULL,

    [product_parent_cd] [varchar](100) NOT NULL,

    [product_id] [int] NOT NULL)

    --------------------------------------------

    INSERT INTO [t_dim_product_temp]

    (product_cd,product_desc,product_parent_cd,product_id)

    SELECT 'TOTPROD','Total Commercial Beverages','',1 UNION ALL

    SELECT 'BT01','Non-Alcoholic Ready-To-Drink Beverages','TOTPROD',2 UNION ALL

    SELECT 'BC02','Packaged WaterRTD','BT01',17 UNION ALL

    SELECT 'BS991','Packaged Water PlainRTD','BC02',60 UNION ALL

    SELECT 'BS999','Packaged WaterEnhancedRTD','BC02',19 UNION ALL

    SELECT 'BS998','Packaged Water BulkRTD','BS991',18 UNION ALL

    SELECT 'BS010','Packaged Water PouchRTD','BS991',50 UNION ALL

    SELECT 'BC01','Beer','BT02',33 UNION ALL

    SELECT 'BT02','Alcoholic Beverages','TOTPROD',4

    ;

    WITH w1 AS(

    SELECT

    leaf_cd = product_cd,

    leaf_desc = product_desc,

    leaf_id = product_id,

    product_cd,

    product_parent_cd,

    level = 1

    FROM t_dim_product_temp leafs

    WHERE

    NOT EXISTS(SELECT 1 FROM t_dim_product_temp WHERE product_parent_cd = leafs.product_cd)

    UNION ALL

    SELECT

    leaf_cd,

    leaf_desc,

    leaf_id,

    t.product_cd,

    t.product_parent_cd,

    level+1

    FROM T_dim_product_temp t

    JOIN w1 ON w1.product_parent_cd = t.product_cd

    ), leafs AS (

    SELECT DISTINCT leaf_cd,

    leaf_desc,

    leaf_id,

    lvl = max(level) OVER(PARTITION BY leaf_cd),

    maxl = max(level) OVER()

    FROM w1

    ), tally AS (

    SELECT top(SELECT top(1) maxl FROM leafs) level

    FROM (Values (1),(2),(3),(4),(5),(6),(7),(8) ) x(Level) -- simplest tally

    ), expd AS (

    SELECT product_cd = cast(leaf_cd + 'd01' as VARCHAR(100)),

    product_parent_cd = leaf_cd,

    product_desc = leaf_desc,

    product_id = leaf_id,

    level = lvl+1

    FROM leafs

    WHERE lvl < maxl

    UNION ALL

    SELECT product_cd = cast(expd.product_cd + 'd01' as VARCHAR(100)),

    product_parent_cd = expd.product_cd,

    product_desc,

    product_id,

    level = expd.level+1

    FROM expd

    JOIN tally ON tally.Level = expd.level+1

    )

    SELECT *

    FROM expd

    ORDER BY product_id, level

    ;

    Don't expect it to fly too fast, due to recursion.

  • Hi Serg,

    It worked for me. Thanks a lot for your quick response.

    Instead of hardcoding the level values 1..8 can we do it dynamically based on the max level of the table?

  • SELECT top(SELECT top(1) maxl FROM leafs) level

    FROM (...)

    exactly selects rows according to maximum level.

    Use tally function of choice instead of ... . See http://www.sqlservercentral.com/articles/Tally+Table/72993/ for great tally construction code.

  • Hi Serg,

    I have gone through the link, which you have provided in order to use the dynamic levels instead of hardcoding values. I am very new to SQL Server technology,particularly to CTE's and hence did not get the exact approach to handle this.

    Could you please help me on this.

  • Specifically the piece of code on fig.7 that you may want to adapt.

    First it provides for max 10000 rows to be generated and then restricts the number of rows needed and ROW_NUMBERs the rows. Query optimizer is smart enough to apply TOP() early so only exactly needed number of rows is produced at any query step.

    Returning to your problem, i suppose this hierarchy will never have more then 25 levels. Although it's quite safe to leave max 10000 as it is, i simplfied it a bit.

    WITH w1 AS(

    SELECT

    leaf_cd = product_cd,

    leaf_desc = product_desc,

    leaf_id = product_id,

    product_cd,

    product_parent_cd,

    level = 1

    FROM t_dim_product_temp leafs

    WHERE

    NOT EXISTS(SELECT 1 FROM t_dim_product_temp WHERE product_parent_cd = leafs.product_cd)

    UNION ALL

    SELECT

    leaf_cd,

    leaf_desc,

    leaf_id,

    t.product_cd,

    t.product_parent_cd,

    level+1

    FROM T_dim_product_temp t

    JOIN w1 ON w1.product_parent_cd = t.product_cd

    ), leafs AS (

    SELECT DISTINCT leaf_cd,

    leaf_desc,

    leaf_id,

    lvl = max(level) OVER(PARTITION BY leaf_cd),

    maxl = max(level) OVER()

    FROM w1

    ), E1(N) AS (-- 5 rows

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1

    ), E2(N) AS (-- 25 rows

    SELECT 1 FROM E1 a, E1 b

    ), tally(level) AS (

    SELECT TOP (SELECT top(1) maxl FROM leafs)

    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E2

    ), expd AS (

    SELECT product_cd = cast(leaf_cd + 'd01' as VARCHAR(100)),

    product_parent_cd = leaf_cd,

    product_desc = leaf_desc,

    product_id = leaf_id,

    level = lvl+1

    FROM leafs

    WHERE lvl < maxl

    UNION ALL

    SELECT product_cd = cast(expd.product_cd + 'd01' as VARCHAR(100)),

    product_parent_cd = expd.product_cd,

    product_desc,

    product_id,

    level = expd.level+1

    FROM expd

    JOIN tally ON tally.Level = expd.level+1

    )

    SELECT *

    FROM expd

    ORDER BY product_id, level

    ;

    Hope it helps.

  • Thanks a lot. Your solution helped me a lot.

  • Hi Serg,

    I have other query to modify the existimg code. My data is looking like this

    CREATE TABLE [t_dim_product_temp](

    [product_cd] [varchar](100) NOT NULL PRIMARY KEY CLUSTERED,

    [product_desc] [varchar](100) NOT NULL,

    [product_parent_cd] [varchar](100) NOT NULL,

    [product_id] [int] NOT NULL)

    --------------------------------------------

    INSERT INTO [t_dim_product_temp]

    (product_cd,product_desc,product_parent_cd,product_id)

    SELECT 'TOTPROD','Total Commercial Beverages','',1 UNION ALL

    SELECT 'BT01','Non-Alcoholic Ready-To-Drink Beverages','TOTPROD',2 UNION ALL

    SELECT 'BC02','Packaged WaterRTD','BT01',17 UNION ALL

    SELECT 'BS991','Packaged Water PlainRTD','BC02',60 UNION ALL

    SELECT 'BS999','Packaged WaterEnhancedRTD','BC02',19 UNION ALL

    SELECT 'BS998','Packaged Water BulkRTD','BS991',18 UNION ALL

    SELECT 'BS010','Packaged Water PouchRTD','BS991',50 UNION ALL

    SELECT 'BC01','Beer','BT02',33 UNION ALL

    SELECT 'BT02','Alcoholic Beverages','TOTPROD',4 UNION ALL

    SELECT 'BC03','Coffee RTD','BT01',5 UNION ALL

    SELECT 'BS004','Packaged RTD Coffee','BC03',6

    From the above query by you the output is like this:

    product_cdproduct_parent_cd product_desc product_idlevel

    BS999d01BS999 Packaged WaterEnhancedRTD195

    BC01d01 BC01 Beer 334

    BC01d01d01BC01d01 Beer 335

    BS004d01BS004 Packaged RTD Coffee65

    I would like to have the query output as below. (for example: referring only one product BS004)

    product_cdproduct_parent_cd product_desc product_id

    BS004d01BC03 Packaged RTD Coffee6

    BS004 BS004d01 Packaged RTD Coffee6

    Please help me on this.

  • Then original

    SELECT 'BS004','Packaged RTD Coffee','BC03',6

    will contradict the query result

    BS004 BS004d01 Packaged RTD Coffee6

    Is it OK? At least you should take this into account in your further steps.

  • Yes. Here my intention is, if the max level of table is '5', need to balance the other records in the table to the level 5.

    For example: If the records has only 3 levels then 3rd level should be moved to 5th level and then 3rd and 4th levels need to be created.

    Thank in advance.

  • OK try this. We just need remember leaf's parent, start generating results at the leaf level contrary to level+1 in previous script and adjust final product assignment.

    CREATE TABLE [t_dim_product_temp](

    [product_cd] [varchar](100) NOT NULL PRIMARY KEY CLUSTERED,

    [product_desc] [varchar](100) NOT NULL,

    [product_parent_cd] [varchar](100) NOT NULL,

    [product_id] [int] NOT NULL)

    --------------------------------------------

    INSERT INTO [t_dim_product_temp]

    (product_cd,product_desc,product_parent_cd,product_id)

    SELECT 'TOTPROD','Total Commercial Beverages','',1 UNION ALL

    SELECT 'BT01','Non-Alcoholic Ready-To-Drink Beverages','TOTPROD',2 UNION ALL

    SELECT 'BC02','Packaged WaterRTD','BT01',17 UNION ALL

    SELECT 'BS991','Packaged Water PlainRTD','BC02',60 UNION ALL

    SELECT 'BS999','Packaged WaterEnhancedRTD','BC02',19 UNION ALL

    SELECT 'BS998','Packaged Water BulkRTD','BS991',18 UNION ALL

    SELECT 'BS010','Packaged Water PouchRTD','BS991',50 UNION ALL

    SELECT 'BC01','Beer','BT02',33 UNION ALL

    SELECT 'BT02','Alcoholic Beverages','TOTPROD',4 UNION ALL

    SELECT 'BC03','Coffee RTD','BT01',5 UNION ALL

    SELECT 'BS004','Packaged RTD Coffee','BC03',6;

    WITH w1 AS(

    SELECT

    leaf_cd = product_cd,

    leaf_parent_cd = product_parent_cd,

    leaf_desc = product_desc,

    leaf_id = product_id,

    product_cd,

    product_parent_cd,

    level = 1

    FROM t_dim_product_temp leafs

    WHERE

    NOT EXISTS(SELECT 1 FROM t_dim_product_temp WHERE product_parent_cd = leafs.product_cd)

    UNION ALL

    SELECT

    leaf_cd,

    leaf_parent_cd,

    leaf_desc,

    leaf_id,

    t.product_cd,

    t.product_parent_cd,

    level+1

    FROM T_dim_product_temp t

    JOIN w1 ON w1.product_parent_cd = t.product_cd

    ), leafs AS (

    SELECT DISTINCT leaf_cd,

    leaf_parent_cd,

    leaf_desc,

    leaf_id,

    lvl = max(level) OVER(PARTITION BY leaf_cd),

    maxl = max(level) OVER()

    FROM w1

    ), E1(N) AS (-- 5 rows

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1

    ), E2(N) AS (-- 25 rows

    SELECT 1 FROM E1 a, E1 b

    ), tally(level) AS (

    SELECT TOP (SELECT top(1) maxl FROM leafs)

    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E2

    ), expd AS (

    SELECT leaf_cd, maxl,

    product_cd = cast(leaf_cd + 'd01' as VARCHAR(100)),

    product_parent_cd = leaf_parent_cd,

    product_desc = leaf_desc,

    product_id = leaf_id,

    level = lvl

    FROM leafs

    WHERE lvl < maxl

    UNION ALL

    SELECT leaf_cd, maxl,

    product_cd = case expd.level+1

    when maxl then leaf_cd

    else cast(expd.product_cd + 'd01' as VARCHAR(100)) end,

    product_parent_cd = expd.product_cd,

    product_desc,

    product_id,

    level = expd.level+1

    FROM expd

    JOIN tally ON tally.Level = expd.level+1

    )

    SELECT *

    FROM expd

    ORDER BY product_id, level

    ;

    P.S. Misprint missing ; corrected

  • Hi Serg,

    Thanks for your quick reply.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply