Solved

Hieararchy Table & Select problem

Posted on 2015-01-02
6
114 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql, how to change the data type after data loading? 9 61
SQL: Transformation or Pivot 3 37
Report 8 28
Modifying Powershell to list all server with SQL Server running ? 4 27
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

710 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