create table table3
( 编号 int not NULL,
a int NOT NULL,
b int NOT NULL,
c int NOT NULL,
d int NOT NULL
)insert into table3 values(4,45,46,47,13)
insert into table3 values(25,48,49,15,50)
insert into table3 values(6,124,34,55,33)
( 编号 int not NULL,
a int NOT NULL,
b int NOT NULL,
c int NOT NULL,
d int NOT NULL
)insert into table3 values(4,45,46,47,13)
insert into table3 values(25,48,49,15,50)
insert into table3 values(6,124,34,55,33)
create table table3
( 编号 int not NULL,
a int NOT NULL,
b int NOT NULL,
c int NOT NULL,
d int NOT NULL
)insert into table3 values(4,45,46,47,13)
insert into table3 values(25,48,49,15,50)
insert into table3 values(6,124,34,55,33)select 编号 ,
max(case px when 1 then val else 0 end) A,
max(case px when 2 then val else 0 end) B,
max(case px when 3 then val else 0 end) C,
max(case px when 4 then val else 0 end) D
from
(
select m.* , px = (select count(1) from
(
select 编号 , a val from table3
union all
select 编号 , b val from table3
union all
select 编号 , c val from table3
union all
select 编号 , d val from table3
) n where n.编号 = m.编号 and n.val < m.val) + 1 from
(
select 编号 , a val from table3
union all
select 编号 , b val from table3
union all
select 编号 , c val from table3
union all
select 编号 , d val from table3
) m
) k
group by 编号drop table table3/*
编号 A B C D
----------- ----------- ----------- ----------- -----------
4 13 45 46 47
6 33 34 55 124
25 15 48 49 50(所影响的行数为 3 行)
*/--sql 2005使用row_number+行列转换实现.
create table table3
( 编号 int not NULL,
a int NOT NULL,
b int NOT NULL,
c int NOT NULL,
d int NOT NULL
)insert into table3 values(4,45,46,47,13)
insert into table3 values(25,48,49,15,50)
insert into table3 values(6,124,34,55,33)select 编号 ,
max(case px when 1 then val else 0 end) A,
max(case px when 2 then val else 0 end) B,
max(case px when 3 then val else 0 end) C,
max(case px when 4 then val else 0 end) D
from
(
select m.* , px = row_number() over(partition by 编号 order by val) from
(
select 编号 , a val from table3
union all
select 编号 , b val from table3
union all
select 编号 , c val from table3
union all
select 编号 , d val from table3
) m
) k
group by 编号drop table table3/*
编号 A B C D
----------- ----------- ----------- ----------- -----------
4 13 45 46 47
6 33 34 55 124
25 15 48 49 50(3 行受影响)
*/