Andy Green
asked on
Hieararchy Table & Select problem
Hi
I have a self referencing location table, and in any select I want to show the upper locations to be appended to the location.
Declare @Location TABLE
(
Location_ID int,
ParentLocation_ID int,
Location varchar(20)
)
insert into @Location values
(1, null, 'Location 1'),
(2, 1, 'Location 1A'),
(3, 1, 'Location 1B'),
(4, 1, 'Location 1C'),
(5, 4, 'Location 1Ca'),
(6, 4, 'Location 1Cb'),
(7, 4, 'Location 1Cc'),
(8, null, 'Location 1'),
(9, 8, 'Location A'),
(10, 8, 'Location B')
Select Location_ID, ParentLocation_ID, Location From @Location
This select returns for example 'Location 1Cc' but I want it to return 'Location 1 - Location 1C' - Location 1Cc'
I'm playing with ,With Hierarchy but cant figure it out.
Andy
I have a self referencing location table, and in any select I want to show the upper locations to be appended to the location.
Declare @Location TABLE
(
Location_ID int,
ParentLocation_ID int,
Location varchar(20)
)
insert into @Location values
(1, null, 'Location 1'),
(2, 1, 'Location 1A'),
(3, 1, 'Location 1B'),
(4, 1, 'Location 1C'),
(5, 4, 'Location 1Ca'),
(6, 4, 'Location 1Cb'),
(7, 4, 'Location 1Cc'),
(8, null, 'Location 1'),
(9, 8, 'Location A'),
(10, 8, 'Location B')
Select Location_ID, ParentLocation_ID, Location From @Location
This select returns for example 'Location 1Cc' but I want it to return 'Location 1 - Location 1C' - Location 1Cc'
I'm playing with ,With Hierarchy but cant figure it out.
Andy
Here's the output for the second SELECT statement (i.e., my SELECT statement). Scroll to the right for the FullLocation.
Location_ID ParentLocation_ID Location FullLocation
----------- ----------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
1 NULL Location 1 Location 1
8 NULL Location 1 Location 1
9 8 Location A Location 1 - Location A
10 8 Location B Location 1 - Location B
2 1 Location 1A Location 1 - Location 1A
3 1 Location 1B Location 1 - Location 1B
4 1 Location 1C Location 1 - Location 1C
5 4 Location 1Ca Location 1 - Location 1C - Location 1Ca
6 4 Location 1Cb Location 1 - Location 1C - Location 1Cb
7 4 Location 1Cc Location 1 - Location 1C - Location 1Cc
ASKER
Thanks Phillip
What version of SQL are you using - I get the following error
Msg 195, Level 15, State 10, Line 29
'CONCAT' is not a recognized built-in function name.
Andy
What version of SQL are you using - I get the following error
Msg 195, Level 15, State 10, Line 29
'CONCAT' is not a recognized built-in function name.
Andy
ASKER
Looks like this is my fault, should have specified SQL 2008.
I'll award the points and submit again.
Andy
I'll award the points and submit again.
Andy
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent thank you.
Andy
Andy
Open in new window
First time I've used a string instead of a number for the recursion.