can this be done? - SQL Server Forums

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)

can this be done?

Author

Topic

tehprince
Starting Member

9 Posts

Posted - 2008-03-13 : 10:41:34

I have the following table:

id geounit parentgeounit

Here's some sample data:
1 Northeast null
2 Southeast null
3 New Jersey 1
4 Florida 2

Basically I need to return the parentgeounit's name, for instance for record #4 I'd want to return

4 Florida Southeast
Instead of:
4 Florida 2

Can this be accomplished?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-13 : 11:09:52

You can actualy join the table to itself!

DECLARE @table TABLE (ID int,geoUnit varchar(20),parent int)

INSERT INTO @table

SELECT 1,' Northeast' , null
UNION ALL
SELECT 2,'Southeast' , null
UNION ALL
SELECT 3, 'New Jersey', 1
UNION ALL
SELECT 4, 'Florida' , 2

SELECT a.ID,b.geounit,a.geounit
FROM @table a
INNER JOIN
@table b
ON
a.id = b.parent

Jim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-13 : 12:01:09

How many levels can there be?
Maybe you should look at using a recursive cte?

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-13 : 12:26:36

I think you need to write a recursive CTE to get you this. Have a look at recursive CTEs in BOL.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-13 : 13:19:38

I had a minute between meetings, here is a Recurive CTE sample based on the data from Jimf:

WITH GeoUnit
AS
(
	SELECT *, CAST(NULL AS VARCHAR(20)) AS ParentUnit, 0 AS Level
	FROM @Table
	WHERE Parent IS NULL

	UNION ALL

	SELECT 
		T.*,GeoUnit.GeoUnit AS ParentUnit, Level + 1
	FROM 
		@Table AS T
	INNER JOIN
		GeoUnit
		ON T.Parent = GeoUnit.ID
)

SELECT *
FROM GeoUnit

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-14 : 02:03:25

quote:
Originally posted by visakh16

I think you need to write a recursive CTE to get you this. Have a look at recursive CTEs in BOL.

That's great. Maybe he didn't understand the first time, due to my Swedish accent?

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources