Solved

SQL Sorting & ordering help needed

Posted on 2015-01-15
9
123 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

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…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

773 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