Solved

Hierarchial tree with child count, indentation and dynamic levels

Posted on 2004-10-12
9
433 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:
ScottPletcher 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

911 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

22 Experts available now in Live!

Get 1:1 Help Now