Solved

Hierarchial tree with child count, indentation and dynamic levels

Posted on 2004-10-12
9
434 Views
Last Modified: 2006-11-17
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
Comment
Question by:amiteshsinha
  • 3
  • 2
9 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 12288720
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
 
LVL 6

Expert Comment

by:izblank
ID: 12289990
Can you provide the relevant table definitions and the code you have tryed so far?
0
 

Author Comment

by:amiteshsinha
ID: 12291423
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:amiteshsinha
ID: 12291465
I need to maintain an order by parent_id,option_slno,option_id

thanks
0
 
LVL 6

Assisted Solution

by:izblank
izblank earned 250 total points
ID: 12292001
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
 
LVL 6

Expert Comment

by:izblank
ID: 12499238
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

776 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