求对5列数据的横向排序问题?谢先

Question

编程思路如下：
对A表中的5个列进行编程，分别对第1行，第2行，直到最后一行的数值进行从小到大的排序，把所排的结果分别插入到B表中的b1,b2,b3,b4,b5列中。注意，是按行进行排序，范例如下：表A中的数据结构如下：
a1    a2   a3   a4   a5
1     5    42   6    7
5     0    32   45   9
15    4    4    7   35
8     37   74   2    37
.
.
.
所求出的B表中的数据结构如下:
b1    b2   b3   b4   b5
1     5    6    7    42
0     5    9    32   45
4     4    7    15   35
2     8    37   37   74
.
.
.这是对原来的问题进行了引申了一下,哪位能否编一下,谢谢!

Answer 1

create table #t1(a1 int,a2 int,a3 int,a4 int,a5 int)
insert into #t1 select 1 ,5 ,42,6 ,7
insert into #t1 select 5 ,0 ,32,45,9
insert into #t1 select 15,4 ,4 ,7 ,35
insert into #t1 select 8 ,37,74,2 ,37select identity(int,1,1) as id,* into #t2 from #t1select
    b1=max(case num when 1 then a end),
    b2=max(case num when 2 then a end),
    b3=max(case num when 3 then a end),
    b4=max(case num when 4 then a end),
    b5=max(case num when 5 then a end)
from
    (select
         a.id,a.a,count(b.id) as num
     from
         (select id,a1 as a,1 as nid from #t2 union all 
          select id,a2,2 as nid from #t2 union all 
          select id,a3,3 as nid from #t2 union all 
          select id,a4,4 as nid from #t2 union all 
          select id,a5,5 as nid from #t2) a,
         (select id,a1 as a,1 as nid from #t2 union all 
          select id,a2,2 as nid from #t2 union all 
          select id,a3,3 as nid from #t2 union all 
          select id,a4,4 as nid from #t2 union all 
          select id,a5,5 as nid from #t2) b 
     where
         a.id=b.id and (a.a>b.a or (a.a=b.a and a.nid<=b.nid))
     group by
         a.id,a.a,a.nid) c
group by
    c.id/*
b1          b2          b3          b4          b5          
----------- ----------- ----------- ----------- ----------- 
1           5           6           7           42
0           5           9           32          45
4           4           7           15          35
2           8           37          37          74
*/drop table #t1,#t2

Answer 2

--建立测试表及数据
create table taba(a1 int,a2 int,a3 int,a4 int,a5 int)
select 1,     5  ,  42 ,  6   , 7 union all
select 5,     0 ,   32 ,  45 ,  9 union all
select 15,    4  ,  4  ,  7  , 35 union all
select 8,    37  , 74  , 2   , 37create table tabb(b1 int,b2 int,b3 int,b4 int,b5 int)--处理过程
declare @a1 int,@a2 int,@a3 int,@a4 int,@a5 int
declare @t table(col int)
declare o_cur cursor local for
        select a1,a2,a3,a4,a5 from taba
open o_cur
fetch next from o_cur into @a1,@a2,@a3,@a4,@a5
while(@@fetch_status=0)
   begin
      insert @t select @a1 union all select @a2 union all select @a3
                union all select @a4 union all select @a5
      select id=identity(int,1,1),col into #i from @t order by col
      select @a1=col from #i where id=1
      select @a2=col from #i where id=2
      select @a3=col from #i where id=3
      select @a4=col from #i where id=4
      select @a5=col from #i where id=5
      insert tabb(b1,b2,b3,b4,b5)values(@a1,@a2,@a3,@a4,@a5)     
      drop table #i
      delete @t
      fetch next from o_cur into @a1,@a2,@a3,@a4,@a5
   end
close o_cur
deallocate o_cur--得数据select * from tabb/*
b1          b2          b3          b4          b5          
----------- ----------- ----------- ----------- ----------- 
1           5           6           7           42
0           5           9           32          45
4           4           7           15          35
2           8           37          37          74
*/

Answer 3

--进行两次排序，每次都选出最大和最小放在头和尾列select case when a1<=a2 and a1<=a3 and a1<=a4 and a1<=a5 then a1 
            when a2<=a1 and a2<=a3 and a2<=a4 and a2<=a5 then a2  
            when a3<=a1 and a3<=a2 and a3<=a4 and a3<=a5 then a3  
            when a4<=a1 and a4<=a2 and a4<=a3 and a4<=a5 then a4  
            when a5<=a1 and a5<=a2 and a5<=a3 and a5<=a4 then a5 
            end as a1, --选出最小的
       case when a2<=a1 and a2<=a3 and a2<=a4 and a2<=a5 then a1
            when a2>=a1 and a2>=a3 and a2>=a4 and a2>=a5 then a5 
            else a2 as a2,  --若a2最小则与a1置换，若最大则与a5置换，否则不变
       case when a3<=a1 and a3<=a2 and a3<=a4 and a3<=a5 then a1  
            when a3<=a1 and a3>=a2 and a3>=a4 and a3>=a5 then a5
            else a3 as a3 ,
       case when a4<=a1 and a4<=a2 and a4<=a3 and a4<=a5 then a1 
            when a4>=a1 and a4>=a2 and a4>=a3 and a4>=a5 then a5 
            else a4 as a4 ,
       case when a1>=a2 and a1>=a3 and a1>=a4 and a1>=a5 then a1 
            when a2>=a1 and a2>=a3 and a2>=a4 and a2>=a5 then a2  
            when a3<=a1 and a3>=a2 and a3>=a4 and a3>=a5 then a3  
            when a4>=a1 and a4>=a2 and a4>=a3 and a4>=a5 then a4  
            when a5>=a1 and a5>=a2 and a5>=a3 and a5>=a4 then a5 
            end as a5  --选出最大的
from #t1 
into #t2 
--第二次只对a2,a3,a4三列进行排序就行了
select case when a2<=a3 and a2<=a4 then a2  
            when a3<=a2 and a3<=a4 then a3  
            when a4<=a2 and a4<=a3 then a4  
            end as a2, 
       case when a3<=a2 and a3<=a4 then a2
            when a3>=a2 and a3>=a4 then a4 
            else a3 as a3,
       case when a2>=a3 and a2>=a4 then a2  
            when a3>=a2 and a3>=a4 then a3  
            when a4>=a2 and a4>=a3 then a4  
            end as a4  
from #t2 
into #t3select * from #t3   --即得结果

Answer 4

-- sql 2005SELECT [1] as a1, [2] as a2, [3] as a3, [4] as a4, [5] as a5
FROM(
SELECT ROW, col, Row1 = ROW_NUMBER() OVER(PARTITION BY ROW ORDER BY col)
FROM(
SELECT *, ROW=ROW_NUMBER() OVER(ORDER BY a1)
FROM #t1
)DATA
UNPIVOT(
col FOR colname IN(a1, a2, a3, a4, a5)
)U
)DATA
PIVOT(
MAX(col)
FOR row1 IN([1], [2], [3], [4], [5]) 
)P

Answer 5

-- 测试数据
create table #t1(a1 int,a2 int,a3 int,a4 int,a5 int)
insert into #t1 select 1 ,5 ,42,6 ,7
insert into #t1 select 5 ,0 ,32,45,9
insert into #t1 select 15,4 ,4 ,7 ,35
insert into #t1 select 8 ,37,74,2 ,37
GO-- 查询
SELECT [1] as a1, [2] as a2, [3] as a3, [4] as a4, [5] as a5
FROM(
SELECT ROW, col, Row1 = ROW_NUMBER() OVER(PARTITION BY ROW ORDER BY col)
FROM(
SELECT *, ROW=ROW_NUMBER() OVER(ORDER BY a1)
FROM #t1
)DATA
UNPIVOT(
col FOR colname IN(a1, a2, a3, a4, a5)
)U
)DATA
PIVOT(
MAX(col)
FOR row1 IN([1], [2], [3], [4], [5]) 
)P
GO-- 删除测试数据
DROP TABLE #t1/*-- 结果
a1          a2          a3          a4          a5
----------- ----------- ----------- ----------- -----------
1           5           6           7           42
0           5           9           32          45
2           8           37          37          74
4           4           7           15          35(4 行受影响)--*/