How to change columns Order - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to change columns Order

Author

Topic

Clages1
Yak Posting Veteran

69 Posts

Posted - 2007-01-27 : 10:02:06

Hi, i have a table Named PRICE like this

Code Price1 , Price2 , Price3, Price4
A 100 80 50 20
b 120 90 0 0
C 150 0 0 0
D 200 45 20 0

In fact any combination, but Price1 is the highest and Price4 the
Lowest

i would like to do a UPdate changing to

A 20 50 80 100
b 90 120 0 0
C 150 0 0 0
D 20 45 200

I was trying using case, but i can not get right results

any help will be apreciated

Tks

C Lages

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-27 : 14:38:06

I don't understand your requirements. What determines the order the values in the output, and why does the last row have a column missing?

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-28 : 01:44:15

[code]
-- create the function that perform the sorting
create function f_sort
(
@pos int,
@val1 int,
@val2 int,
@val3 int,
@val4 int
)
returns int
as
begin
declare @t table
(
id int identity(1,1),
value int
)
insert into @t(value)
select value
from
(
select @val1 as value union all
select @val2 as value union all
select @val3 as value union all
select @val4
) v
order by case when value <> 0 then 1 else 2 end, value
return (select value from @t where id = @pos)
end
go
[/code]

[code]
-- create table for testing
declare @price table
(
Code varchar(10),
Price1 int,
Price2 int,
Price3 int,
Price4 int
)

-- insert test data into table for testing
insert into @price
select 'A', 100, 80, 50, 20 union all
select 'B', 120, 90, 0, 0 union all
select 'C', 150, 0, 0, 0 union all
select 'D', 200, 45, 20, 0

-- Original data
select * from @price
/*
Code Price1 Price2 Price3 Price4
---------- ----------- ----------- ----------- -----------
A 100 80 50 20
B 120 90 0 0
C 150 0 0 0
D 200 45 20 0
*/

-- Perform the update
update p
set
Price1 = dbo.f_sort(1, Price1, Price2, Price3, Price4),
Price2 = dbo.f_sort(2, Price1, Price2, Price3, Price4),
Price3 = dbo.f_sort(3, Price1, Price2, Price3, Price4),
Price4 = dbo.f_sort(4, Price1, Price2, Price3, Price4)
from @price p

-- After update
select * from @price
/*
Code Price1 Price2 Price3 Price4
---------- ----------- ----------- ----------- -----------
A 20 50 80 100
B 90 120 0 0
C 150 0 0 0
D 20 45 200 0
*/
[/code]

Clages1
Yak Posting Veteran

69 Posts

Posted - 2007-01-29 : 11:39:12

Kthan

Tks for the answer
you help me lot
i did some adjustment,but
your query did what i need

tks
again

C. Lages

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources