November 17, 2015 at 5:24 am
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?
November 17, 2015 at 10:57 am
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
November 17, 2015 at 10:13 pm
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.
November 18, 2015 at 2:44 am
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.
November 18, 2015 at 4:18 am
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.
November 18, 2015 at 5:07 am
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?
November 18, 2015 at 5:19 am
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.
November 18, 2015 at 11:11 pm
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.
November 19, 2015 at 12:56 am
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.
November 19, 2015 at 3:08 am
Thanks a lot. Your solution helped me a lot.
November 24, 2015 at 12:29 am
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.
November 24, 2015 at 1:37 am
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.
November 24, 2015 at 1:57 am
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.
November 24, 2015 at 2:23 am
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
November 24, 2015 at 10:48 pm
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