September 5, 2008 at 9:36 am
Hi What im trying to do is build a menu in asp.net/SQL 2005 backend all examples are that the parent and child are integers so thats what I want. ALso would be good to know how to do this.
I have a table
CREATE TABLE [dbo].[Hierarchy3](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Prod_group] [varchar](12) NULL,
[sub_group] [varchar](12) NULL,
[descr] [varchar](50) NULL,
CONSTRAINT [PK_Hierarchy3] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Insert into hierarchy3 (prod_group,[sub_group]) values (NULL,'CAT-A')
Insert into hierarchy3 (prod_group,[sub_group]) values (NULL,'CAT-B')
Insert into hierarchy3 (prod_group,[sub_group]) values (NULL,'CAT-C')
Insert into hierarchy3 (prod_group,[sub_group]) values (NULL,'CAT-D')
Insert into hierarchy3 (prod_group,[sub_group]) values (NULL,'CAT-E')
Insert into hierarchy3 (prod_group,[sub_group]) values (NULL,'CAT-F')
Insert into hierarchy3 (prod_group,[sub_group]) values (NULL,'CAT-G')
Insert into hierarchy3 (prod_group,[sub_group]) values (NULL,'CAT-H')
Insert into hierarchy3 (prod_group,[sub_group]) values (NULL,'CAT-I')
Insert into hierarchy3 (prod_group,[sub_group]) values ('CAT-A','CC')
Insert into hierarchy3 (prod_group,[sub_group]) values ('CAT-B','ALLWH')
Insert into hierarchy3 (prod_group,[sub_group]) values ('CAT-B','BLUE')
Insert into hierarchy3 (prod_group,[sub_group]) values ('CAT-C','CREAM')
Insert into hierarchy3 (prod_group,[sub_group]) values ('CAT-D','FLOW')
Insert into hierarchy3 (prod_group,[sub_group]) values ('CAT-E','FRE')
Insert into hierarchy3 (prod_group,[sub_group]) values ('CAT-F','KAND')
Insert into hierarchy3 (prod_group,[sub_group]) values ('CC','SubSubCat')
September 5, 2008 at 9:40 am
You can use a recursive Common Table Expression to select the hierarchy. Books Online/MSDN has a great example of how to do that. Is that what you're asking about?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 5, 2008 at 10:19 am
No i want to be able to generate the hierachy as integer values
September 5, 2008 at 10:51 am
I guess I don't see the problem. Assign an integer value to each level and go from there.
An ID field will probably suffice for that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply