Andy Green
asked on
SQL Sorting & ordering help needed
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
use a WHERE in between lines 33 and 34.
ASKER
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
if @ShowFacilities is a 0 it returns everything as per original, which is wrong and with a 1 it returns nothing
ASKER
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
Andy
ASKER
Its because IsFacility is null in the CTE. I'm on it like a tramp on chips.
Andy
Andy
ASKER
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
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
ASKER
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
select Location from myTable
Where isnull(IsFacility,0) = 0
Union
select Location from myTable
Where isnull(IsFacility,0) = @ShowFacilities
order by mynumber
ASKER
Andy