• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 119
  • Last Modified:

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
0
Andy Green
Asked:
Andy Green
  • 3
  • 3
1 Solution
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Love the problem. Here's the solution:

Declare  @Location TABLE
( 
Location_ID int,
ParentLocation_ID int,
Location varchar(100)
)

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;

With myLocation as
(Select Location_ID, ParentLocation_ID, Location, Location as FullLocation
from @Location as LO
Where ParentLocation_ID is null

union all

select LO.Location_ID, LO.ParentLocation_ID, LO.Location, CONVERT(VARCHAR(100),CONCAT(ML.FullLocation, ' - ' , LO.Location))
from @Location as LO
inner join myLocation ML
on LO.ParentLocation_ID = ML.Location_ID
where LO.ParentLocation_ID is not null)

SELECT * FROM myLocation

Open in new window


First time I've used a string instead of a number for the recursion.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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

Open in new window

0
 
Andy GreenAuthor Commented:
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
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
Andy GreenAuthor Commented:
Looks like this is my fault, should have specified SQL 2008.

I'll award the points and submit again.

Andy
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Change

CONCAT(ML.FullLocation, ' - ' , LO.Location)

to

ML.FullLocation + ' - ' + LO.Location

So you have:

Declare  @Location TABLE
( 
Location_ID int,
ParentLocation_ID int,
Location varchar(100)
)

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;

With myLocation as
(Select Location_ID, ParentLocation_ID, Location, Location as FullLocation
from @Location as LO
Where ParentLocation_ID is null

union all

select LO.Location_ID, LO.ParentLocation_ID, LO.Location, CONVERT(VARCHAR(100), ML.FullLocation + ' - ' + LO.Location)
from @Location as LO
inner join myLocation ML
on LO.ParentLocation_ID = ML.Location_ID
where LO.ParentLocation_ID is not null)

SELECT * FROM myLocation

Open in new window

0
 
Andy GreenAuthor Commented:
Excellent thank you.
Andy
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now