Solved

Hieararchy Table & Select problem

Posted on 2015-01-02
6
109 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

948 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now