Hierarchial tree with child count, indentation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Hierarchial tree with child count, indentation

Author

Topic

prabhakarc
Starting Member

8 Posts

Posted - 2004-10-12 : 12:11:25

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.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-12 : 12:16:03

something like my photos section??

http://www.seventhnight.com/photos.asp

a short article on how to do it:
http://www.seventhnight.com/treestructs.asp

EDIT: Just removing a bad link..

Corey

prabhakarc
Starting Member

8 Posts

Posted - 2004-10-12 : 14:34:45

Thanks but my result set should be some what like this :
option_id option_name option_slno parent_id child_count
11742 |....Site Plan Options 332 0 2

later

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-12 : 14:46:36

give us some sample data to work with and I'm sure someone will give you a good headstart...

Corey

Kristen
Test

22859 Posts

Posted - 2004-10-12 : 15:27:02

SELECT '|' + REPLICATE('.', MyTreeLevel) + MyBranchName
FROM MyTable

Kristen

prabhakarc
Starting Member

8 Posts

Posted - 2004-10-12 : 16:10:39

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

I need to maintain an order by parent_id,option_slno,option_id

thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-12 : 16:48:03

prabhakarc
Starting Member

8 Posts

Posted - 2004-10-12 : 17:03:39

Hi ,
Here is some sample data :
----------------------------

"H38" "583" 583 "Add Twin Window" "579" "S" "Y" "P" "" "" " "
"H16" "141" 141 "Standard Plan" "139" "S" "Y" "P" "" "" " "
"H38" "584" 5 "Front Double Dormer" "500" "S" "Y" "P" "X X X X" 2002-09-05 02:50:02.217000000 "A" "A"
"H38" "539" 539 "Add Brick to Grade" "506" "S" "Y" "P" "X X X X" 2002-08-30 03:49:59.357000000 "A" "A"
"H38" "984" 984 "1st Upgrade Pad" "980" "S" "Y" "P" "" "" " "
"H38" "1008" 1008 "Cable Outlet" "1002" "S" "Y" "P" "" "" " "
"H38" "1195" 1195 "Hardwood" "1187" "S" "Y" "P" "" "" " "
"H38" "1304" 1304 "3rd Upgrade Hardwood" "1300" "S" "Y" "P" "" "" " "
"H38" "1391" 1391 "Intercom Speaker" "1388" "S" "Y" "P" "" "" " "
"H38" "1478" 1478 "Base Resilient" "1476" "S" "Y" "P" "" "" " "
"H38" "1564" 1564 "Security Keypad" "1562" "S" "Y" "P" "" "" " "
"H38" "1649" 1649 "Security Keypad" "1641" "S" "Y" "P" "" "" " "
"H9" "1775" 1775 "Wiring Options" "1744" "S" "Y" "P" "" "" " "
"H9" "1863" 1863 "4th Upgrade Resilient" "1857" "S" "Y" "P" "" "" " "
"H9" "1951" 1951 "2nd Upgrade Hardwood" "1947" "S" "Y" "P" "" "" " "
"H9" "2039" 2039 "5th Upgrade Ceramic" "2035" "S" "Y" "P" "" "" " "
"H9" "2127" 2127 "Hardwood" "2110" "S" "Y" "P" "" "" " "
"H9" "2214" 2214 "Recessed Light w/ Switch" "2202" "S" "Y" "P" "" "" " "
"H19" "9146" 7990 "Ceramic" "9128" "S" "Y" "P" "" "" " "
"H19" "8900" 5 "3rd Upgrade Carpet" "8899" "S" "Y" "P" "" "" " "
"H1" "10444" 7544 "Gas Heat" "10443" "S" "Y" "P" "" "" " "
"H19" "8058" 8058 "Gas Range" "8056" "S" "Y" "P" "" "" " "
"H19" "8605" 4 "2nd Upgrade Hardwood" "8603" "S" "Y" "P" "" "" " "
"H19" "6947" 6947 "Glass Break" "6939" "S" "Y" "P" "" "" " "
"H19" "7894" 2 "Upgrade 1" "7891" "S" "Y" "P" "X X X X" 2002-09-22 23:41:29.293000000 "A" "A"
"H19" "7084" 7084 "Security Keypad" "7082" "S" "Y" "P" "" "" " "
"H19" "8325" 23 "Flooring" "6517" "S" "Y" "P" "" "" " "
"H19" "10192" 8686 "Stainless Steel" "10190" "S" "Y" "P" "" "" " "
"H19" "10371" 6776 "Telephone Outlet" "10369" "S" "Y" "P" "" "" " "
"H19" "7293" 7293 "Glass Break" "7292" "S" "Y" "P" "" "" " "
"H19" "9956" 4 "2nd Upgrade Carpet" "9955" "S" "Y" "P" "" "" " "
"H19" "10226" 8770 "Standard" "10225" "S" "Y" "P" "" "" " "
"H126" "11062" 7 "Add Stone to Grade on Side" "11059" "S" "Y" "P" "" "" " "
"H126" "12239" 12239 "Electric Fireplace" "11151" "S" "Y" "P" "X X X X" 2002-12-19 00:30:51.467000000 "A" "A"
"H117" "12367" 3 "Site Plan Revision" "12365" "S" "Y" "P" "" 2004-10-08 00:00:00 "" " "
"H53" "11746" 333 "Structural Options" "0" "S" "Y" "P" "" "" " "
"H126" "12341" 5 "Upgrade 4" "12338" "S" "Y" "P" "" "" " "
"H126" "11469" 8253 "Standard" "11467" "S" "Y" "P" "" "" " "
"H126" "11556" 7988 "Resilient" "11545" "S" "Y" "P" "" "" " "
"H130" "12399" 6869 "Recessed Light w/ Switch" "12396" "S" "Y" "P" "" "" " "
"H130" "12817" 3 "Flood Lights Rear" "12803" "S" "Y" "P" "" "" " "
"H130" "12575" 6685 "Additional 110 Outlet" "12562" "S" "Y" "P" "" "" " "
"H130" "12661" 8077 "Recessed Light w/ Switch" "12654" "S" "Y" "P" "" "" " "
"H130" "12753" 12241 "Box Baywindow" "12637" "S" "Y" "P" "" "" " "
"H131" "18316" 7989 "Hardwood" "18303" "S" "N" "A" "xxxx" 2002-12-27 23:49:54.733000000 "H53" "A"
"H131" "18333" 6884 "Cable Outlet" "18321" "S" "N" "A" "xxxx" 2002-12-27 23:49:54.733000000 "H53" "A"
"H131" "18329" 6880 "Intercom Speaker" "18321" "S" "N" "A" "xxxx" 2002-12-27 23:49:54.733000000 "H53" "A"

The hierarchy that you mentioned at the end is correct .The child count should also be part of the result set i.e. in a column in the temp table in the above posted code.

thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-12 : 17:06:38

yeah...thats what i meant... if no one else gets to this, i'll get to it later tonight... i'm leaving work !

Corey

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-13 : 15:29:23

I've been looking through the sample data you provided, and none of the parents are in the list...

see the following test:

quote:


CREATE TABLE #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 
)

Insert Into #house_type_options
Select 'H38', '583', 583, 'Add Twin Window', '579', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H16', '141', 141, 'Standard Plan', '139', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H38', '584', 5, 'Front Double Dormer', '500', 'S', 'Y', 'P', 'X X X X', '2002-09-05 02:50:02.217', 'A', 'A'
Union All Select 'H38', '539', 539, 'Add Brick to Grade', '506', 'S', 'Y', 'P', 'X X X X', '2002-08-30 03:49:59.357', 'A', 'A'
Union All Select 'H38', '984', 984, '1st Upgrade Pad', '980', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H38', '1008', 1008, 'Cable Outlet', '1002', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H38', '1195', 1195, 'Hardwood', '1187', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H38', '1304', 1304, '3rd Upgrade Hardwood', '1300', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H38', '1391', 1391, 'Intercom Speaker', '1388', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H38', '1478', 1478, 'Base Resilient', '1476', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H38', '1564', 1564, 'Security Keypad', '1562', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H38', '1649', 1649, 'Security Keypad', '1641', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H9', '1775', 1775, 'Wiring Options', '1744', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H9', '1863', 1863, '4th Upgrade Resilient', '1857', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H9', '1951', 1951, '2nd Upgrade Hardwood', '1947', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H9', '2039', 2039, '5th Upgrade Ceramic', '2035', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H9', '2127', 2127, 'Hardwood', '2110', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H9', '2214', 2214, 'Recessed Light w/ Switch', '2202', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H19', '9146', 7990, 'Ceramic', '9128', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H19', '8900', 5, '3rd Upgrade Carpet', '8899', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H1', '10444', 7544, 'Gas Heat', '10443', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H19', '8058', 8058, 'Gas Range', '8056', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H19', '8605', 4, '2nd Upgrade Hardwood', '8603', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H19', '6947', 6947, 'Glass Break', '6939', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H19', '7894', 2, 'Upgrade 1', '7891', 'S', 'Y', 'P', 'X X X X', '2002-09-22 23:41:29.293', 'A', 'A'
Union All Select 'H19', '7084', 7084, 'Security Keypad', '7082', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H19', '8325', 23, 'Flooring', '6517', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H19', '10192', 8686, 'Stainless Steel', '10190', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H19', '10371', 6776, 'Telephone Outlet', '10369', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H19', '7293', 7293, 'Glass Break', '7292', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H19', '9956', 4, '2nd Upgrade Carpet', '9955', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H19', '10226', 8770, 'Standard', '10225', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H126', '11062', 7, 'Add Stone to Grade on Side', '11059', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H126', '12239', 12239, 'Electric Fireplace', '11151', 'S', 'Y', 'P', 'X X X X', '2002-12-19 00:30:51.467', 'A', 'A'
Union All Select 'H117', '12367', 3, 'Site Plan Revision', '12365', 'S', 'Y', 'P', null, '2004-10-08 00:00:00', null, null
Union All Select 'H53', '11746', 333, 'Structural Options', '0', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H126', '12341', 5, 'Upgrade 4', '12338', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H126', '11469', 8253, 'Standard', '11467', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H126', '11556', 7988, 'Resilient', '11545', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H130', '12399', 6869, 'Recessed Light w/ Switch', '12396', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H130', '12817', 3, 'Flood Lights Rear', '12803', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H130', '12575', 6685, 'Additional 110 Outlet', '12562', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H130', '12661', 8077, 'Recessed Light w/ Switch', '12654', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H130', '12753', 12241, 'Box Baywindow', '12637', 'S', 'Y', 'P', null, null, null, null
Union All Select 'H131', '18316', 7989, 'Hardwood', '18303', 'S', 'N', 'A', 'xxxx', '2002-12-27 23:49:54.733', 'H53', 'A'
Union All Select 'H131', '18333', 6884, 'Cable Outlet', '18321', 'S', 'N', 'A', 'xxxx', '2002-12-27 23:49:54.733', 'H53', 'A'
Union All Select 'H131', '18329', 6880, 'Intercom Speaker', '18321', 'S', 'N', 'A', 'xxxx', '2002-12-27 23:49:54.733', 'H53', 'A'

Select * From #house_type_options

Select * 
From #house_type_options A
Inner Join #house_type_options B
On convert(A.option_Id = B.parent_Id

Drop Table #house_type_options

Am I missing something, or is the data just not a good sample set??

Corey

prabhakarc
Starting Member

8 Posts

Posted - 2004-10-13 : 18:06:09

Hi,
I just copy pasted some data as i could not paste the entire data.If you can give me your email id , i can send the sample data as attachment.

thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-13 : 18:26:07

I don't want a huge amount of data... let me try to create sample data for your table layout... just enough to demonstrate.

Am I correct in assuming the parent_id is the option_id of the parent record?

oId text pId
1 Outside/Yard 0
2 ...extra trees 1

I will see what kind of example I can come up with.

Corey

prabhakarc
Starting Member

8 Posts

Posted - 2004-10-14 : 10:13:07

Your assumption "parent_id is the option_id of the parent record" is correct .

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-14 : 13:22:57

in this example you can see herarquie between tables (tblClasification ==> tblItem) and between records
within the same table determined by the field itemLevel (itemLevel 1 is the parent of itemLevel 2 within
the same class)

Declare @tblClasification table(
    class char(2),
    cDesc nvarchar(20))

Declare @tblItem table(
    class char(2),
    itemLevel tinyint,
    itemNum int,
    item nvarchar(20))

Insert into @tblClasification
    Select 'FR','Fruit' union
    Select 'AN','Animal' union
    Select 'TR','Transport'

Insert into @tblItem
    Select 'FR',1, 0,'Melon' union
    Select 'FR',2, 1,'Water Melon' union
    Select 'FR',2, 2,'Cantelope' union
    Select 'FR',1, 10,'Citric' union
    Select 'FR',2, 11,'Orange' union
    Select 'FR',2, 12,'Mandarine' union
    Select 'FR',2, 13,'Grape Fruit' union
    Select 'AN',1, 0,'Feline' union
    Select 'AN',2, 1,'Cat' union
    Select 'AN',2, 2,'Lion' union
    Select 'AN',2, 3,'Lince' union
    Select 'TR',1, 0,'Ground' union
    Select 'TR',2, 1,'Car' union
    Select 'TR',2, 2,'Bike' union
    Select 'TR',2, 3,'Train' union
    Select 'TR',1, 10,'Air' union
    Select 'TR',2, 11,'Airplane' union
    Select 'TR',2, 11,'Jet' union
    Select 'TR',2, 11,'Bolloon'


Declare @results table(
    prefix nvarChar(5),
    cDesc nvarchar(20),
    itemLevel tinyint,
    itemNum int,
    item nvarchar(20))

Insert into @results
    Select   '|' prefix, cDesc ,null itemLevel,null itemNum,null item
    From     @tblClasification
    union
    SELECT   '|' + REPLICATE('--', itemLevel)prefix, cDesc,itemLevel,itemNum,item
    FROM     @tblClasification a inner join @tblItem b on
             a.class = b.class

Select   prefix + case When ItemLevel is Null then cDesc else '' end + isNull(item,'')  as itemTree
From     @results
Order by cDesc,itemNum,itemLevel

prabhakarc
Starting Member

8 Posts

Posted - 2004-10-14 : 14:19:45

I would prefer not to disturb the existing
table structure in the sense for example
the existing structure does not define an itemLevel as you have mentioned.

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-14 : 15:21:36

quote:
Originally posted by prabhakarc

I would prefer not to disturb the existing
table structure in the sense for example
the existing structure does not define an itemLevel as you have mentioned.

I just edited my last post

I'm not sujesting you to change the existing table structure I just created two simple tables to relationate and added some sample data as an example.

In my example I used a variable table @results where I load all the data and then manipulated it to retrieve it in a tree form. Maybe if you don't have a numeric field that indicates the item level you can make up a field in the results table so you can use something like REPLICATE('--', itemLevel)

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

prabhakarc
Starting Member

8 Posts

Posted - 2004-10-14 : 15:44:45

Thanks.This might give me a headstart.I will get back to you soon.

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-14 : 16:18:12

Maybe this will help you more I haven't tested and
I have a feeling it won't work but I think it will help you some how

I assummed that option_Id = parent_Id are parent records
and option_Id <> parent_Id are child records
try it with and without rollup if count for each child record is 1
then with rollup you would have the count of children in the last child record for each parent and there should be some of the other fields with null so you will know this is the record with totals.

Well tell us how you solved it when you do

Declare @myTree table (
option_id         nvarchar (50) ,
option_name       nvarchar (100),
option_slno       int ,
parent_id         nvarchar (100)
itemLevel         tinyint,
childCount        int)

Insert into @myTree
Select   option_id, option_name, option_slno, parent_id, 1,null as childCount
From     house_type_options
Where    option_Id = parent_Id
Union
Select   option_id, option_name, option_slno, parent_id, 2,count(*)  as childCount
From     house_type_options
Where    option_Id <> parent_Id
Group by option_id, option_name, option_slno, parent_id --with rollup

Select   prefix + case When ItemLevel is Null then cDesc else '' end + ' - ' + option_id 
          + ' - ' + option_name + ' - ' + option_slno + ' - ' + parent_id,childCount
From     @results
Order by cDesc,itemNum,itemLevel

It will be easier for me to help you if you place data in the form of

insert into @myTable
Select bla,bla,bla union
Select bla,bla,bla ...

lets say of the top 3 parent_ID

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources