October 12, 2004 at 10:11 am
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.
October 12, 2004 at 2:19 pm
Surely there's a client side reporting tool that does this,
but if not, why not use a stored proc to create the result set?
October 12, 2004 at 2:46 pm
I did develop a recursive stored procedure but when i try to include the child count , i am losing the efficiency of the SP is gone.
Here is the store procedure 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
October 13, 2004 at 11:37 am
Welcome to the forum Joe,
I have tried, all be it poorly, to explain your nested set hierarchy in a few posts.
I use it a lot; it works very well for me.
Also for anyone reading this, SQL for smarties is a great resource, It is sitting, pages ragged and curling next to my machine.
thanks again Joe.
Tal McMahon
October 13, 2004 at 12:14 pm
BTW,
Here is a Query that will work against a nested set Hierarchy that will return a column that you can use for indenting.
SELECT T1.pk_location AS pk_Item,
(COUNT(T2.pk_location)-1) AS indentation,
T1.location AS Item
FROM h_location AS T1,
h_location AS T2
WHERE T1.
BETWEEN T2.
AND T2.
AND t1.IsInactive=0
GROUP BY T1.pk_location,
T1.location,
T1.
ORDER BY T1.
HTH
Tal McMahon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply