Creating a Derived Column "Level", by parent ID

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

General SQL Server Forums

New to SQL Server Programming

Creating a Derived Column "Level", by parent ID

Author

Topic

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-02-24 : 11:06:28

I have a table as follows

ID NAME PARENTID
1 Group 1: Culture and Recreation NULL
2 Culture and Arts 1
3 Media and communications. 2
4 Visual arts, architecture, ceramic art. 2
5 Performing arts. 2
6 Historical, literary, and humanistic societies. 2
7 Museums. 2
8 Zoos and aquariums. 2

What Im looking for is a derived column i.e level which will either return 1,2 or 3

i.e if the record has no parent id then level 1
if only 1 parent id then level 2, else if more then level 3.

Any Ideas ?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-24 : 11:25:25

can you also post the required output as per your above sample data?

Cheers
MIK

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-02-24 : 11:26:32

With SQL2005 and above, you can use recursion:


-- *** Test Data ***
CREATE TABLE #t
(
	ID int NOT NULL
	,[Name] varchar(50) NOT NULL
	,ParentID int NULL
)
INSERT INTO #t
SELECT 1, 'Group 1: Culture and Recreation', NULL
UNION ALL SELECT 2, 'Culture and Arts', 1
UNION ALL SELECT 3, 'Media and communications.', 2
UNION ALL SELECT 4, 'Visual arts, architecture, ceramic art.', 2
UNION ALL SELECT 5, 'Performing arts.', 2
UNION ALL SELECT 6, 'Historical, literary, and humanistic societies.', 2
UNION ALL SELECT 7, 'Museums.', 2
UNION ALL SELECT 8, 'Zoos and aquariums.', 2
-- *** End Test Data ***

;WITH rCTE
AS
(
	SELECT ID, [Name], ParentID, 1 AS rlevel
	FROM #t
	WHERE ParentID IS NULL
	
	UNION ALL
	
	SELECT T.ID, T.[Name], T.ParentID, R.rlevel + 1
	FROM #t T
		JOIN rCTE R
			ON T.ParentID = R.ID
)
SELECT *
FROM rCTE

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-02-24 : 11:58:38

Expected Data is as such

1 Group 1: Culture and Recreation NULL 1
2 Culture and Arts 1 2
3 Media and communications. 2 3
4 Visual arts, architecture, ceramic art. 2 3
5 Performing arts. 2 3
6 Historical, literary, and humanistic societies. 2 3
7 Museums. 2 3
8 Zoos and aquariums. 2 3

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-24 : 12:15:59

use what ifor suggested

Cheers
MIK

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-02-25 : 04:01:40

Yup that worked cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources