27,579
社区成员
发帖
与我相关
我的任务
分享
create table #t(BM varchar(4), A numeric(18,2), B numeric(18,2), C numeric(18,2) ,
D numeric(18,2), E numeric(18,2), F numeric(18,2))
insert INTO #t
select'BM',1.2,2.3,1.3,2.1,4.3,5.6
GO
create function dbo.fn_sort(
@a numeric(18,2),
@b numeric(18,2),
@c numeric(18,2),
@d numeric(18,2),
@e numeric(18,2),
@f numeric(18,2),
@flag int)
Returns int
AS
begin
declare @re int
select @re=XX.tmp
from
(
select X.flag,X.col,tmp=row_number() over(order by X.Col desc)
from
(
select 1 as flag, @a as Col
union all
select 2 as flag,@b as Col
union all
select 3 as flag,@c as Col
union all
select 4 as flag,@d as Col
union all
select 5 as flag,@e as Col
union all
select 6 as flag,@f as Col
) X
) XX
where XX.flag=@flag
return @re
end
GO
select 'Sort' as BM,
dbo.fn_sort(A,B,C,D,E,F,1) as A,
dbo.fn_sort(A,B,C,D,E,F,2) as B,
dbo.fn_sort(A,B,C,D,E,F,3) as C,
dbo.fn_sort(A,B,C,D,E,F,4) as D,
dbo.fn_sort(A,B,C,D,E,F,5) as E,
dbo.fn_sort(A,B,C,D,E,F,6) as F
from #t
/*
Sort 6 3 5 4 2 1
*/
GO
drop table #t
drop function dbo.fn_sort
create table #t(F varchar(2), A float, B float, C float ,D float, E float, F float)
insert INTO #t
select'BM','1.2','2.3','1.3','2.1','4.3','5.6'
select *from #t
union all
select 'SORT',* from (select rn=row_number() over (order by val desc),col from #t)
unpivot(val for col in (A,B,C,D,E,F))b
)t
pivot(max(rn) for col in(A,B,C,D,E,F,G))c
--输出结果
F A B C D E F
BM 1.2,2.3,1.3,2.1,4.3,5.6
SORT 6 3 5 4 2 1
create table #t(BM varchar(2), A varchar(3), B varchar(3), C varchar(3) ,D varchar(3), E varchar(3), F varchar(3))
insert INTO #t
select'BM','1.2','2.3','1.3','2.1','4.3','5.6'
select *from #t
union all
select 'STORT','6','3','5','4','2','1'