Solved

SQL Sorting & ordering help needed

Posted on 2015-01-15
9
125 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Database - Move Tables from one Database to Another 4 50
Copy data to New Year 9 33
help converting varchar to date 14 25
Split string into 3 separate fields 5 22
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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 INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

733 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