Improve company productivity with a Business Account.Sign Up

x
?
Solved

SQL Sorting & ordering help needed

Posted on 2015-01-15
9
Medium Priority
?
133 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 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
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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
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…

585 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