[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Sorting & ordering help needed

Posted on 2015-01-15
9
Medium Priority
?
128 Views
Last Modified: 2015-01-21
Hi All

Due to a limitation on my page display I have to have the data returned from my database in a particular shape,

Its a room booking syatem where I have rooms / desks & PC's (amoungst other things)

irrespective of how the data is input into the database I need it to be displays in order, ie

Location 1
Desk 1
PC 1
Desk2
PC 2
Desk 3
PC 3
Location 2
Desk 4
Desk 5
PC 5
Desk 6



I have this sample data where I deliberately muddled the table, but need the data out to match the above shape.

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

 insert into @Location values
 (1, null, 'Location 1'),
 (2, 1, 'Desk 1'),
 (3, 1, 'Desk 2'),
 (4, 1, 'Desk 3'),
 (5, 6, 'Desk 6'),
 (6, null, 'Location 2'),
 (7, 6, 'Desk 4'),
 (8, 6, 'Desk 5'),
 (9, 2, 'PC 1'),
 (10, 3, 'PC 2'),
 (11, 8, 'PC 5')

  Select Location_ID, ParentLocation_ID, Location  From @Location

Is this possible, and any ideas on how to achieve this.

I'm using SQL Server 2008.
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
  • 7
  • 2
9 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 2000 total points
ID: 40551006
Here you go:

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

 insert into @Location values 
 (1, null, 'Location 1'),
 (2, 1, 'Desk 1'),
 (3, 1, 'Desk 2'),
 (4, 1, 'Desk 3'),
 (5, 6, 'Desk 6'),
 (6, null, 'Location 2'),
 (7, 6, 'Desk 4'),
 (8, 6, 'Desk 5'),
 (9, 2, 'PC 1'),
 (10, 3, 'PC 2'),
 (11, 8, 'PC 5');

 with myTable as
 (Select Location_ID, Location, 1 as steps, right('00000' + convert(nvarchar(5),Location_ID) ,5) + replicate(95,'0') as mynumber
 from @Location L
 where ParentLocation_ID is null

 union all

 select L.Location_ID, L.Location, myT.steps + 1, left(mynumber,(myT.steps+1)*5) + right('00000' + convert(nvarchar(5),L.Location_ID) ,5) + replicate(95 - (myT.steps+1)*5,'0')
 from myTable as MyT
 inner join @Location L  
 on L.ParentLocation_ID = MyT.Location_ID
)
 select Location from myTable
 order by mynumber

Open in new window


gives this result

Location 1
Desk 1
PC 1
Desk 2
PC 2
Desk 3
Location 2
Desk 6
Desk 4
Desk 5
PC 5

Open in new window

0
 
LVL 3

Author Closing Comment

by:Andy Green
ID: 40551012
Thanks Phillip, you helped me last time too. Much appreciated.

Andy
0
 
LVL 3

Author Comment

by:Andy Green
ID: 40562305
Hi Phillip

I have another issue that I'm struggling with - happy to re post for points.

PC's & Desksnow have a flag IsFacility and I need to filter them based on a param in the proc @IsFacility.

1 show them, 0 don't show them.  I'm still learning CTE form your examples, ca you provide any pointers.

Andy
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40562346
use a WHERE in between lines 33 and 34.
0
 
LVL 3

Author Comment

by:Andy Green
ID: 40562399
Yeah! tried all that, I have added IsFacility to the table defs and added this  isnull(IsFacility,0) = @ShowFacilities,

if @ShowFacilities is a 0 it returns everything as per original, which is wrong and with a 1 it returns nothing
0
 
LVL 3

Author Comment

by:Andy Green
ID: 40562405
What I'm trying to do is hide PC's & Desks which have an IsFacility flag if @ShowFacilities is a 0, and show them in the correct position if its a 1.

Andy
0
 
LVL 3

Author Comment

by:Andy Green
ID: 40562416
Its because IsFacility is null in the CTE. I'm on it like a tramp on chips.

Andy
0
 
LVL 3

Author Comment

by:Andy Green
ID: 40562445
OK Found it, I had the wrong alias.

Now it works but its not right and this is where I got to.

It either returns just the locaitons if set to 0 which is right, but just the Desks & PC (the facilities) if set to 1, this isn't right, I want it to return the locations this ht e facilities in the correct order as original solution.

Shall I copy this to a new posting with fresh points?

Andy
0
 
LVL 3

Author Comment

by:Andy Green
ID: 40562466
I have it woking now as expected. I have s selects with a Union I'm happy with this unless there is a more elegant solution.

select Location from myTable
Where isnull(IsFacility,0) = 0

Union

select Location from myTable
Where isnull(IsFacility,0) = @ShowFacilities

 order by mynumber
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
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 shrink a transaction log file down to a reasonable size.

649 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