1 2 3 4
1 A B C D
2 E F G H
3 I J K
1 2 3 4
1
2 A B C
3 D E F G
4 H I J K
--drop table #temp create table #temp ( seq int , 1 varchar(20) , 2 varchar(20) , 3 varchar(20) , 4 varchar(20) ) insert #temp values(1, 'a', 'b', 'c', 'd') insert #temp values(2, 'e', 'f', 'g', 'h') insert #temp values(3, 'i', 'j', 'k', null) create table #dumy(seq int) insert #dumy values(1) insert #dumy values(2) insert #dumy values(3) insert #dumy values(4) insert #dumy values(5) insert #dumy values(6) insert #dumy values(7) insert #dumy values(8) insert #dumy values(9) insert #dumy values(10) insert #dumy values(11) insert #dumy values(12) insert #dumy values(13) insert #dumy values(14) insert #dumy values(15) insert #dumy values(16) insert #dumy values(17) insert #dumy values(18) insert #dumy values(19) insert #dumy values(20)
declare
@col_cnt int
, @set_num int
set @col_cnt = 4
set @set_num = 9 --蠍一 螻
select
isnull(min(case when b.cnt1 = a.seq then col end), '') 1
, isnull(min(case when b.cnt2 = a.seq then col end), '') 2
, isnull(min(case when b.cnt3 = a.seq then col end), '') 3
, isnull(min(case when b.cnt4 = a.seq then col end), '') 4
from (
select
b.seq + (@col_cnt * (a.seq - 1)) + @set_num - 1 seq
, case
when b.seq = 1 then 1
when b.seq = 2 then 2
when b.seq = 3 then 3
when b.seq = 4 then 4
end col
from #temp a cross join #dumy b
where b.seq <= @col_cnt) a right join (
select
(b.seq * (@col_cnt)) - @col_cnt + 1 cnt1
, (b.seq * (@col_cnt)) - @col_cnt + 2 cnt2
, (b.seq * (@col_cnt)) - @col_cnt + 3 cnt3
, (b.seq * (@col_cnt)) - @col_cnt + 4 cnt4
, a.seq
from #temp a right join #dumy b
on a.seq + @set_num/@col_cnt = b.seq
where (select count(*) from #temp) + @set_num/@col_cnt >= b.seq) b
on a.seq in (b.cnt1, b.cnt2, b.cnt3, b.cnt4)
group by b.cnt1
--蟆郁骸 Top朱 朱 .
/*
1 2 3 4
-------------------- -------------------- -------------------- --------------------
a b c d
e f g h
i j k
*/