• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 445
  • Last Modified:

Hierarchial tree with child count, indentation and dynamic levels

Hi,
I need to return a result set that represents a hierarchial tree with indentation , levels and child count in an optimal way. Something like this :
|....Misc
|.....Upgrade Landscaping
|.....Flood Lights Front
|.....Flood Lights Rear
|.....Humidfier
|.....Air Cleaner
|.....test1
|.....Rough In Basement Full Bath
|....test
|....Site Plan Options
|.....Per Plan Garage Right

The glitch here is i am unable to include the child / subchild count at all levels within the same result set in an optimal way.
The number of levels are dynamic in the sense , they might be n levels today , n+1 tomorrow and so on.
Any thoughts on this is greatly appreciated.
0
amiteshsinha
Asked:
amiteshsinha
  • 3
  • 2
2 Solutions
 
Scott PletcherSenior DBACommented:
The only way I can see to try to maintain an accurate count is via trigger(s).  The trigger(s) would add (or subtract) one from all appropriate levels.  As the counts may become wrong, you would also need a SP that would re-calculate a single hierarchy total or all totals.  Sorry but this code is too complex and "tricky" for me to want to try to code it.  Hopefully someone else has some code or can provide some that works.
0
 
izblankCommented:
Can you provide the relevant table definitions and the code you have tryed so far?
0
 
amiteshsinhaAuthor Commented:
Here is the Store Procedure Code So Far :
------------------------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


ALTER         PROC dbo.GenerateOptionTree
(
           @Root nVarChar(50),
             @HouseType nVarChar(50)
)
AS
BEGIN -- Start Of Main
      SET NOCOUNT ON
      DECLARE @OptionID nVarChar(50), @OptionName nvarchar(200),@House_Type nVarChar(50),@Option_ID int,@Option_SlNo int,
                                        @Show_Flag char(2),@Parent_Id int,@Option_Price varchar(50),
                                        @MarkUp varchar(50),@Estimate_Cost varchar(50),@ChildCount varchar(50)

      SET @OptionName = (SELECT h.option_name FROM dbo.house_type_options h left join option_cost oc on h.option_id=oc.option_id and h.house_type=oc.house_type_id
                           WHERE h.option_id = @Root and h.house_type = @HouseType)
        SET @Option_ID =  (SELECT CAST(h.option_id AS int) FROM dbo.house_type_options h left join option_cost oc on h.option_id=oc.option_id and h.house_type=oc.house_type_id
                           WHERE h.option_id = @Root and h.house_type = @HouseType)
        SET @Option_SlNo =(SELECT h.option_slno FROM dbo.house_type_options h left join option_cost oc on h.option_id=oc.option_id and h.house_type=oc.house_type_id
                           WHERE h.option_id = @Root and h.house_type = @HouseType)
        SET @Show_Flag =  (SELECT h.show_flag FROM dbo.house_type_options h left join option_cost oc on h.option_id=oc.option_id and h.house_type=oc.house_type_id
                           WHERE h.option_id = @Root and h.house_type = @HouseType)
        SET @Parent_Id =  (SELECT cast(h.parent_id as int) FROM dbo.house_type_options h left join option_cost oc on h.option_id=oc.option_id and h.house_type=oc.house_type_id
                           WHERE h.option_id = @Root and h.house_type = @HouseType)
        SET @Option_Price=(SELECT oc.option_price FROM dbo.house_type_options h left join option_cost oc on h.option_id=oc.option_id and h.house_type=oc.house_type_id
                           WHERE h.option_id = @Root and h.house_type = @HouseType)
        SET @MarkUp =     (SELECT oc.mark_up FROM dbo.house_type_options h left join option_cost oc on h.option_id=oc.option_id and h.house_type=oc.house_type_id
                           WHERE h.option_id = @Root and h.house_type = @HouseType)
        SET @Estimate_Cost =(SELECT oc.estimate_cost FROM dbo.house_type_options h left join option_cost oc on h.option_id=oc.option_id and h.house_type=oc.house_type_id
                           WHERE h.option_id = @Root and h.house_type = @HouseType)
        SET @House_Type = @HouseType -- Required For the Next iteration
      
        IF (@Option_Price IS NULL) -- Then Set it as 0
        Begin
            SET @Option_Price = '0'
        End
        IF (@MarkUp IS NULL) -- Then Set it as 0
        Begin
            SET @MarkUp = '0.00'
        End
        IF (@Estimate_Cost IS NULL) -- Then Set it as 0
        Begin
            SET @Estimate_Cost = '0'
        End
        IF (@Option_ID IS NOT NULL) -- Start inserting into a Temp table
        Begin
           INSERT INTO comstock.dbo.option_tree_temp(option_id,option_name,option_slno,show_flag,parent_id,option_price,mark_up,level,estimate_cost)VALUES(@Option_ID,'|'+ REPLICATE('.', @@NESTLEVEL + 1) + @OptionName,@Option_SlNo,@Show_Flag,@Parent_Id,@Option_Price,@MarkUp,@@NESTLEVEL - 1,@Estimate_Cost)
         --update comstock.dbo.option_tree_temp set child_count = isnull(child_count,0)+1 where [level] = @@NESTLEVEL-1    
        End
        --Get lowest Option_id of the Root
      SET @OptionID = (SELECT MIN(h.option_id) FROM dbo.house_type_options h left join option_cost oc on h.option_id=oc.option_id and h.house_type=oc.house_type_id
                         WHERE h.parent_id = @Root and h.house_type = @HouseType)
        --PRINT(@OptionID + 'before while')
      WHILE @OptionID IS NOT NULL --Generate the Children
      BEGIN
                EXEC dbo.GenerateOptionTree @OptionID , @House_Type              
            SET @OptionID = (SELECT MIN(h.option_id) FROM dbo.house_type_options h left join option_cost oc on h.option_id=oc.option_id and h.house_type=oc.house_type_id
                                 WHERE h.parent_id = @Root and h.option_id > @OptionID and h.house_type = @HouseType)
                         
               
      END      

END -- End Of Main

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



TABLE Defnitions :
----------------------
CREATE TABLE [dbo].[house_type_options] (
      [house_type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [option_id] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [option_slno] [int] NOT NULL ,
      [option_name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [parent_id] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [show_flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [del_flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [status] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [created_by] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [last_updated_date] [datetime] NULL ,
      [created_from] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [created_source] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[option_cost] (
      [option_id] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [house_type_id] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [option_price] [numeric](12, 2) NULL ,
      [mark_up] [numeric](5, 2) NULL ,
      [estimate_cost] [numeric](18, 0) NULL ,
      [at_sale_cost] [numeric](18, 0) NULL ,
      [last_update_date] [datetime] NULL
) ON [PRIMARY]
GO
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
amiteshsinhaAuthor Commented:
I need to maintain an order by parent_id,option_slno,option_id

thanks
0
 
izblankCommented:
There are many articles in Microsoft's SQL Server BOL on how to handle hierarchies.  So I suggest you read and see if their recommendations will do any good for you.

 One thing I noticed in your code is, you insert rows one by one - this is bound to be really slow.  Normally, you insert a seed row (that is, for the root option), then all children rows in one select, then all grandchildren, etc.  something like that:

insert into tempTable
(<column_list>)
select <whatever columns necessary>,@NESTLEVEL
from house_type_options hto, tempTable tt
where tempTable.nestlevel=@NESTLEVEL - 1
and tt.option_id=hto.parent_id

And you repeat the process, incrementing @NESTLEVEL, until at some point no more rows are inserted - and you're done.
If one of the columns is children count, then you your select list will be like that:

select <whatever columns necessary>,@NESTLEVEL,
(SELECT count(*) from house_type_options hto2
  WHERE hto2.parent_id = hto.option_id)




0
 
izblankCommented:
I do not think anyone came up with even a solution - but there were recommendations, so I would split it between izblank and ScottPletcher with grade B or C
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now