Solved

SQL Sorting & ordering help needed

Posted on 2015-01-15
9
120 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
  • 7
  • 2
9 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
Comment Utility
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
Comment Utility
Thanks Phillip, you helped me last time too. Much appreciated.

Andy
0
 
LVL 3

Author Comment

by:Andy Green
Comment Utility
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
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
use a WHERE in between lines 33 and 34.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 3

Author Comment

by:Andy Green
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

728 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

14 Experts available now in Live!

Get 1:1 Help Now