Solved

Hierarchial tree with child count, indentation and dynamic levels

Posted on 2004-10-12
9
432 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
Comment Utility
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
Comment Utility
Can you provide the relevant table definitions and the code you have tryed so far?
0
 

Author Comment

by:amiteshsinha
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:amiteshsinha
Comment Utility
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
Comment Utility
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
Comment Utility
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

772 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

12 Experts available now in Live!

Get 1:1 Help Now