SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Nested Parent Child Query Help Please?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JBelthoff
Posting Yak Master

USA
173 Posts

Posted - 05/29/2013 :  10:51:34  Show Profile  Visit JBelthoff's Homepage  Send JBelthoff an AOL message  Reply with Quote
I have a table of System Details which is capable of nesting any numbers of levels deep in Parent Child relationship. Now I need some guidance on how to query the table to get my desired results.

My desired results would be displayed as each top level immediately followed by the nested child levels to it.

So my desired results would be as follows:

ID	SID	PID	Name
1	14562	0	ARU1
2	14562	0	ARU2
8	14562	2	    BRU1
10	14562	8	        CRU1
9	14562	2	    BRU2
3	14562	0	ARU3
4	14562	0	ARU4

The current data is stored like this:

ID	SID	PID	Name
1	14562	0	ARU1
2	14562	0	ARU2
3	14562	0	ARU3
4	14562	0	ARU4
8	14562	2	BRU1
9	14562	2	BRU2
10	14562	8	CRU1

I have included a setup script below for your convenience.

Thank you for the help.

USE [Test]
GO
/****** Object:  Table [dbo].[SystemDetails]    Script Date: 05/29/2013 10:03:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SystemDetails](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[SID] [int] NOT NULL,
	[PID] [int] NOT NULL CONSTRAINT [DF_SystemDetails_PID]  DEFAULT ((0)),
	[Name] [varchar](255) NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

Set Identity_Insert [dbo].[SystemDetails] On
INSERT INTO [SystemDetails] ([ID],[SID],[PID],[Name])VALUES(1,14562,0,'ARU1')
INSERT INTO [SystemDetails] ([ID],[SID],[PID],[Name])VALUES(2,14562,0,'ARU2')
INSERT INTO [SystemDetails] ([ID],[SID],[PID],[Name])VALUES(3,14562,0,'ARU3')
INSERT INTO [SystemDetails] ([ID],[SID],[PID],[Name])VALUES(4,14562,0,'ARU4')
INSERT INTO [SystemDetails] ([ID],[SID],[PID],[Name])VALUES(8,14562,2,'BRU1')
INSERT INTO [SystemDetails] ([ID],[SID],[PID],[Name])VALUES(9,14562,2,'BRU2')
INSERT INTO [SystemDetails] ([ID],[SID],[PID],[Name])VALUES(10,14562,8,'CRU1')
Set Identity_Insert [dbo].[SystemDetails] Off
GO

Select *
From SystemDetails
GO

Drop Table SystemDetails
GO


JBelthoff
› As far as myself... I do this for fun!

James K
Flowing Fount of Yak Knowledge

3587 Posts

Posted - 05/29/2013 :  11:14:37  Show Profile  Reply with Quote
Here is some code to get you started. Look up MSDN for recursive CTE's. They have examples.
;with cte as
(
	select *, 
		CAST(row_Number() over (order by s.ID) as varchar(32)) as Lvl
	from SystemDetails s
	where s.PID = 0
	union all
	select s.*, 
		CAST(c.Lvl+CAST(row_Number() over (partition by c.Lvl order by s.ID) as varchar(32)) as varchar(32)) as Lvl
	from SystemDetails s
	inner join cte c on c.id = s.pid
)
select *,
	replicate('  ',len(Lvl))+Name as FormattedName -- if you want to format
from cte order by Lvl;
Go to Top of Page

JBelthoff
Posting Yak Master

USA
173 Posts

Posted - 05/29/2013 :  11:47:00  Show Profile  Visit JBelthoff's Homepage  Send JBelthoff an AOL message  Reply with Quote
Excellent James. Thank you.

JBelthoff
› As far as myself... I do this for fun!
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3587 Posts

Posted - 05/29/2013 :  15:57:28  Show Profile  Reply with Quote
Very welcome - glad to be of help!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000