Nested Parent Child Query Help Please? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Nested Parent Child Query Help Please?

Author

Topic

JBelthoff
Posting Yak Master

173 Posts

Posted - 2013-05-29 : 10:51:34

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-29 : 11:14:37

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;

JBelthoff
Posting Yak Master

173 Posts

Posted - 2013-05-29 : 11:47:00

Excellent James. Thank you.

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-29 : 15:57:28

Very welcome - glad to be of help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources