show data in hierarchy form

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

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 4-Dec-11 22:48pm

dilip.aim11

Add a Solution

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')

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Derek Henderson · Accepted Answer · 2011-12-05T04:16:00

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

FastEvo8 · Accepted Answer · 2011-12-06T05:36:00

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

show data in hierarchy form

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0