Rearrange column

Hi!
I'll try again
I want to rearrange a column in a table within a stored procedure.
I got this table with this sample data:
ID Text Displayorder
1 Testing 100
2 Testing2 200
3 Testing3 300
4 Testing4 400
5 Testing5 500
The numbers of rows varies so i want 2 inparameters
(start_ID and End_ID).
Example:
start_ID, End_ID (2,4)
Will return.
ID Text Number
1 Testing 100
2 Testing2 400
3 Testing3 300
4 Testing4 200
5 Testing5 500
Can this be done?
I hope someone can help me.
//MagnusHi Magnus
Assuming that your Id columns are not contiguous then you will need to rank
them and do something like:
DECLARE @.lower int, @.upper int
SELECT @.lower = 2, @.upper = 4
SELECT [ASC].ID, [ASC].[Text],
CASE WHEN [ASC].[ORDER] >= @.lower AND [ASC].[ORDER] <= @.upper THEN
[DESC].DisplayOrder ELSE [ASC].DisplayOrder END AS Number
FROM ( SELECT (Select count(*) FROM MyDisplays M where m.id <= D.id) AS
[Order],
D.id, D.[Text], D.DisplayOrder
FROM MyDisplays D ) [ASC]
JOIN
( SELECT (Select count(*) FROM MyDisplays M where m.id >= D.id) AS [Order],
D.id, D.[Text], D.DisplayOrder
FROM MyDisplays D ) [DESC] ON [ASC].[Order] = [DESC].[ORDER]
You can still use [id] in the case statement if necessary.
John
"Mange" wrote:

> Hi!
> I'll try again
> I want to rearrange a column in a table within a stored procedure.
> I got this table with this sample data:
> ID Text Displayorder
> 1 Testing 100
> 2 Testing2 200
> 3 Testing3 300
> 4 Testing4 400
> 5 Testing5 500
> The numbers of rows varies so i want 2 inparameters
> (start_ID and End_ID).
> Example:
> start_ID, End_ID (2,4)
> Will return.
> ID Text Number
> 1 Testing 100
> 2 Testing2 400
> 3 Testing3 300
> 4 Testing4 200
> 5 Testing5 500
> Can this be done?
> I hope someone can help me.
> //Magnus
>|||Thanks but..
That doesnt save the result into that table.
"John Bell" wrote:
> Hi Magnus
> Assuming that your Id columns are not contiguous then you will need to ran
k
> them and do something like:
> DECLARE @.lower int, @.upper int
> SELECT @.lower = 2, @.upper = 4
> SELECT [ASC].ID, [ASC].[Text],
> CASE WHEN [ASC].[ORDER] >= @.lower AND [ASC].[ORDER] <= @.upper THEN
> [DESC].DisplayOrder ELSE [ASC].DisplayOrder END AS Number
> FROM ( SELECT (Select count(*) FROM MyDisplays M where m.id <= D.id) AS
> [Order],
> D.id, D.[Text], D.DisplayOrder
> FROM MyDisplays D ) [ASC]
> JOIN
> ( SELECT (Select count(*) FROM MyDisplays M where m.id >= D.id) AS [Order],
> D.id, D.[Text], D.DisplayOrder
> FROM MyDisplays D ) [DESC] ON [ASC].[Order] = [DESC].[ORDER]
> You can still use [id] in the case statement if necessary.
> John
>
> "Mange" wrote:
>|||> That doesnt save the result into that table.
No, it does not, but you can *use* it in your procedure to make it do whan
you need. :)
ML|||It doesnt work.
The result is the whole table.
"John Bell" wrote:
> Hi Magnus
> Assuming that your Id columns are not contiguous then you will need to ran
k
> them and do something like:
> DECLARE @.lower int, @.upper int
> SELECT @.lower = 2, @.upper = 4
> SELECT [ASC].ID, [ASC].[Text],
> CASE WHEN [ASC].[ORDER] >= @.lower AND [ASC].[ORDER] <= @.upper THEN
> [DESC].DisplayOrder ELSE [ASC].DisplayOrder END AS Number
> FROM ( SELECT (Select count(*) FROM MyDisplays M where m.id <= D.id) AS
> [Order],
> D.id, D.[Text], D.DisplayOrder
> FROM MyDisplays D ) [ASC]
> JOIN
> ( SELECT (Select count(*) FROM MyDisplays M where m.id >= D.id) AS [Order],
> D.id, D.[Text], D.DisplayOrder
> FROM MyDisplays D ) [DESC] ON [ASC].[Order] = [DESC].[ORDER]
> You can still use [id] in the case statement if necessary.
> John
>
> "Mange" wrote:
>|||Hi
It does what you specified with the data that you gave. You have not
specified what your restriction should be but you should be able to use a
WHERE clause in each of the derived tables to do what you require.
John
"Mange" wrote:
> Thanks but..
> That doesnt save the result into that table.
>
> "John Bell" wrote:
>|||If you actually want to change the data try:
DECLARE @.lower int, @.upper int
SELECT @.lower = 2, @.upper = 4
UPDATE O
SET DisplayOrder = N.DisplayOrder
FROM MyDisplays O
JOIN MyDisplays N ON ( O.id = @.lower AND N.id = @.upper ) OR ( O.id = @.upper
AND N.id = @.lower )
John
"Mange" wrote:
> Thanks but..
> That doesnt save the result into that table.
>
> "John Bell" wrote:
>|||Hi
To be more precise.
The result is exactly like if i would have used Select * from MyDisplays
"John Bell" wrote:
> Hi
> It does what you specified with the data that you gave. You have not
> specified what your restriction should be but you should be able to use a
> WHERE clause in each of the derived tables to do what you require.
> John
> "Mange" wrote:
>|||Many Thanks John you are a star.
I'm sorry about my bad english and my poor knowledge about SQL.
Thanks again it works know.
//Magnus
"John Bell" wrote:
> If you actually want to change the data try:
> DECLARE @.lower int, @.upper int
> SELECT @.lower = 2, @.upper = 4
> UPDATE O
> SET DisplayOrder = N.DisplayOrder
> FROM MyDisplays O
> JOIN MyDisplays N ON ( O.id = @.lower AND N.id = @.upper ) OR ( O.id = @.uppe
r
> AND N.id = @.lower )
> John
> "Mange" wrote:
>