Click here to Skip to main content
15,880,299 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
hi
this is my demo table


SQL
USE [chk]
GO
/****** Object:  Table [dbo].[Demo]    Script Date: 12/05/2011 15:03:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Demo](
	[AC_ID] [bigint] NOT NULL,
	[RootId] [bigint] NULL,
	[parentID] [bigint] NULL,
	[Type] [varchar](50) NULL,
	[Name] [varchar](50) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF



this is my demo data


SQL
INSERT INTO [demo] VALUES(11,2,9,'S','SubSubGroup22')
INSERT INTO [demo] VALUES(12,3,3,'S','SubGroup31')
INSERT INTO [demo] VALUES(13,4,4,'S','SubGroup41')
INSERT INTO [demo] VALUES(14,3,12,'S','SubSubGroup31')
INSERT INTO [demo] VALUES(15,4,13,'S','SubSubGroup41')
INSERT INTO [demo] VALUES(16,3,14,'S','SubSubGroup32')
INSERT INTO [demo] VALUES(17,1,5,'A','AC11')
INSERT INTO [demo] VALUES(18,1,5,'A','AC12')
INSERT INTO [demo] VALUES(19,1,7,'A','AC13')
INSERT INTO [demo] VALUES(20,1,8,'A','AC14')
INSERT INTO [demo] VALUES(21,2,9,'A','AC21')
INSERT INTO [demo] VALUES(22,2,9,'A','AC22')
INSERT INTO [demo] VALUES(23,2,11,'A','AC23')
INSERT INTO [demo] VALUES(24,2,11,'A','AC24')



and this is my procedure




SQL
ALTER PROC [dbo].[ShowHierarchy]
@Root int



as

	DECLARE @ACID int, @ACName varchar(500),@ACType varchar(50),@ACType1 varchar(50)

	SET @ACName = (SELECT [Name]  FROM  dbo.Demo WHERE  AC_ID= @Root)
	SET @ACType = (SELECT  [Type] FROM dbo.Demo WHERE  AC_ID= @Root)
	SET @ACType1 = (SELECT  [Type] FROM dbo.Demo WHERE  AC_ID= @Root)
	print REPLICATE('-', @@NESTLEVEL * 4) + @ACName+'--'  --+  @root
	SET @ACID = (SELECT MIN(AC_ID) FROM Demo WHERE  parentID= @Root)


	WHILE @ACID IS NOT NULL
	BEGIN
		EXEC dbo.ShowHierarchy @ACID
		SET @ACID = (SELECT MIN(AC_ID) FROM Demo WHERE  parentID= @Root  AND AC_ID > @ACID )
	END


whn i run this procedure
like

--exec ShowHierarchy 1


so i getting result like this

SQL
----Group1--
--------SubGroup11--
------------SubSubGroup11--
----------------AC13--
------------SubSubGroup12--
----------------AC14--
------------AC11--
------------AC12--
--------SubGroup12--



but i want result like that


SQL
----Group1--
--------SubGroup11--
------------AC11--
------------AC12--
------------SubSubGroup11--
----------------AC13--
------------SubSubGroup12--
----------------AC14--
--------SubGroup12--




Plese Help Me....
Posted
Comments
Derek Henderson 5-Dec-11 9:12am    
Can you post all the data from the hierarchy
dilip.aim11 5-Dec-11 9:48am    
INSERT INTO [demo] VALUES(1,1,0,'G','Group1')
INSERT INTO [demo] VALUES(2,2,0,'G','Group2')
INSERT INTO [demo] VALUES(3,3,0,'G','Group3')
INSERT INTO [demo] VALUES(4,4,0,'G','Group4')
INSERT INTO [demo] VALUES(5,1,1,'S','SubGroup11')
INSERT INTO [demo] VALUES(6,1,1,'S','SubGroup12')
INSERT INTO [demo] VALUES(7,1,5,'S','SubSubGroup11')
INSERT INTO [demo] VALUES(8,1,5,'S','SubSubGroup12')
INSERT INTO [demo] VALUES(9,2,2,'S','SubGroup21')
INSERT INTO [demo] VALUES(10,2,9,'S','SUBSubGroup21')
INSERT INTO [demo] VALUES(11,2,9,'S','SubSubGroup22')
INSERT INTO [demo] VALUES(12,3,3,'S','SubGroup31')
INSERT INTO [demo] VALUES(13,4,4,'S','SubGroup41')
INSERT INTO [demo] VALUES(14,3,12,'S','SubSubGroup31')
INSERT INTO [demo] VALUES(15,4,13,'S','SubSubGroup41')
INSERT INTO [demo] VALUES(16,3,14,'S','SubSubGroup32')
INSERT INTO [demo] VALUES(17,1,5,'A','AC11')
INSERT INTO [demo] VALUES(18,1,5,'A','AC12')
INSERT INTO [demo] VALUES(19,1,7,'A','AC13')
INSERT INTO [demo] VALUES(20,1,8,'A','AC14')
INSERT INTO [demo] VALUES(21,2,9,'A','AC21')
INSERT INTO [demo] VALUES(22,2,9,'A','AC22')
INSERT INTO [demo] VALUES(23,2,11,'A','AC23')
INSERT INTO [demo] VALUES(24,2,11,'A','AC24')

try this problem was you weren't ordering by Name


ALTER procedure [dbo].[ShowHierarchy]
@Root int
as

DECLARE @ACID int, @ACName varchar(500),@ACType varchar(50),@ACType1 varchar(50)

Select @ACName = Name, @ACType =[Type]
from dbo.Demo where AC_ID = @Root

print REPLICATE('-', @@NESTLEVEL * 4) + @ACName+'--'; --+ @root

SET @ACID = (SELECT Top 1 AC_ID FROM Demo WHERE parentID = @Root Order by Name)
Select @ACName = Name, @ACType =[Type] From dbo.Demo where AC_ID = @ACID

WHILE @ACID IS NOT NULL
BEGIN
EXEC dbo.ShowHierarchy @ACID
SET @ACID = (SELECT Top 1 AC_ID FROM Demo WHERE parentID = @Root AND Name > @ACName Order by Name)
Select @ACName = Name, @ACType =[Type] From dbo.Demo where AC_ID = @ACID
END
 
Share this answer
 
v2
Comments
dilip.aim11 7-Dec-11 5:08am    
Thanks
I would use a CTE avoiding a WHILE loop:
SQL
;WITH cteHierarchy AS
(
	SELECT d.AC_ID
			,d.parentID
			,d.RootId,CAST(N'----' + d.Name + N'--' AS NVARCHAR(MAX)) AS name
			,CAST(d.Name AS NVARCHAR(MAX))+N'/' as lineage
			,CAST(0 AS INT) as lvl
	from dbo.Demo d 
	where d.parentID=0
	UNION ALL
	SELECT d.AC_ID
			,c.AC_ID
			,d.RootId,REPLICATE (N'-',lvl*4+8) + CAST(d.Name AS NVARCHAR(MAX)) + N'--'
			,c.lineage + d.Name + N'/'
			,c.lvl+1
	FROM dbo.Demo d 
	INNER JOIN cteHierarchy c
	ON d.parentID=c.AC_ID
)
SELECT c.name
from cteHierarchy c
order by lineage
 
Share this answer
 
Comments
dilip.aim11 7-Dec-11 5:01am    
Thanks a lot .. U solved My Problem...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900