Solved

Hieararchy Table & Select problem

Posted on 2015-01-02
6
115 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

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…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

636 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