Hirerchy Query Help Required

Question

Post reply

Hirerchy Query Help Required

joshtheflame

Default port

Points: 1499
More actions
August 5, 2010 at 1:49 pm

#224378

Hi Guys,
I have an oracle background and now working on a project which is SQLServer based so stuck and therefore need your help. I have the following tables which I am using for Menu Management and Tree Population.
Menu
----------------------------
menu_id -------> (PK) int
menu_name ----> varchar(30)
Menu_Template
-------------------------------
node_id -----> int
node_name --> varchar(50)
parent_id ----> int
menu_id -----> (FK) int
I have a stored procedure which is like the following
ALTER PROCEDURE [dbo].[sp_Get_Menus]
@p_menu_id int
AS
BEGIN
SELECT node_id, node_name, parent_id, node_type, link, hide, menu_id from Menu_Template
WHERE (menu_id = @p_menu_id OR @p_menu_id IS NULL) order by node_id
END
In oracle we used to add spaces in every child with LEVEL and SPACE but here in SQLServer I dont know how to create the following output in the above stored procedure.
Root
---Administrator
-------Child (A)
-------Child (B)
---------Sub Child (B1)
-------Child (C)
---------Sub Child (C1)
---Networks
-------Cisco
For example purpose I have added "-" but i want spaces here before every child so I can use the query to fill my DropDown Combo in ASp.net control. Please use my above stored procedure and make changes in it.
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Adiga One Orange Chip Points: 27284 More actions · Answer 1

You may use ' ' or CHAR(32) to include spaces as in the below example.

select ' ' + 'Name'

select char(32)+char(32)+'Name'

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

joshtheflame Default port Points: 1499 More actions · Answer 2

Thanks for your reply but it seems you didnt get my questions properly. Adding space before every node is not what i am looking for infact I want spaces before node as per LEVEL.

Victor Dvoryaninov SSC Veteran Points: 208 More actions · Answer 3

use tempdb

go

if OBJECT_ID('[dbo].[Menu]', 'U') is null begin

create table [dbo].[Menu]

(

menu_id int primary key,

menu_name varchar(100)

)

insert [dbo].[Menu] select 1,'Root'

end

if OBJECT_ID('[dbo].[Menu_Template]','U') is null begin

create table [dbo].[Menu_Template]

(

node_id int,

node_name varchar(50),

parent_id int,

menu_id int

)

insert [dbo].[Menu_Template] select 1,'Administrator',null,1

insert [dbo].[Menu_Template] select 2,'Child (A)',1,1

insert [dbo].[Menu_Template] select 3,'Child (B)',1,1

insert [dbo].[Menu_Template] select 4,'Child (C)',1,1

insert [dbo].[Menu_Template] select 5,'Sub Child (B1)',3,1

insert [dbo].[Menu_Template] select 6,'Sub Child (C1)',4,1

insert [dbo].[Menu_Template] select 7,'Networks',null,1

insert [dbo].[Menu_Template] select 8,'Cisco',7,1

end

declare @menu_id int = 1

;with

CTE as

(

select

[LEVEL]=1, ---<<<----

node_id,

node_name,

parent_id,

full_path=cast( node_name as varchar(8000))

from [dbo].[Menu_Template]

where menu_id = @menu_id

and parent_id is null

UNION ALL

select

[LEVEL]=[LEVEL]+1, ---<<<----

m.node_id,

m.node_name,

m.parent_id,

full_path=cast( full_path+'-'+m.node_name as varchar(8000))

from CTE c inner join [dbo].[Menu_Template] m on c.node_id = m.parent_id

where menu_id = @menu_id

)

select node_name

from

(

select

node_name=menu_name,

full_path=''

from [dbo].[Menu]

where menu_id = @menu_id

union all

select

node_name=REPLICATE('--',[LEVEL] + 1)+ node_name,

full_path

from CTE

)m

order by full_path

joshtheflame Default port Points: 1499 More actions · Answer 4

Victor I tried your procedure its working fine but having issues related to ordering...the ordering of 2nd level items are not the same as it was entered.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

CELKO (8/6/2010)
Have you ever looked at the Nested Sets model for trees and hierarchies? It is a lot easier than the Oracle "mock pointer chain" traversal or the recursive CTE used in SQL Server. Get a copy of TREES & HIERARCHIES IN SQL for several other ways to do this sort of task.

Hey Joe! To help folks find your books, you might want to add the ISBN to each title. 🙂

If you were really clever, you'd turn each title into a link... :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

joshtheflame (8/6/2010)
Victor I tried your procedure its working fine but having issues related to ordering...the ordering of 2nd level items are not the same as it was entered.

Since the ordered entered can't be guaranteed without a sort on an IDENTITY column, you'll need to make sure to add an Identity column to your table(s).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions