Solved

Hieararchy Table & Select problem

Posted on 2015-01-02
6
113 Views
Last Modified: 2015-01-02
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
Comment
Question by:Andy Green
  • 3
  • 3
6 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40527416
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40527418
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
 
LVL 3

Author Comment

by:Andy Green
ID: 40527637
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 3

Author Comment

by:Andy Green
ID: 40527642
Looks like this is my fault, should have specified SQL 2008.

I'll award the points and submit again.

Andy
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40527658
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
 
LVL 3

Author Closing Comment

by:Andy Green
ID: 40527663
Excellent thank you.
Andy
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question