declare @t table(Cato varchar(6),Nam varchar(6),Num varchar(6))
insert into @t select 'A',rtrim('A+ '),10
insert into @t select 'B',rtrim('AA+ '),10
insert into @t select 'C',rtrim('AC+ '),10
insert into @t select 'D',rtrim('A++ '),10
insert into @t select 'E',rtrim('ABC+'),10
insert into @t select 'F',rtrim('AEF+'),10select
Cato1=max(case id%4 when 1 then Cato end),
Nam1 =max(case id%4 when 1 then Nam end),
Num1 =max(case id%4 when 1 then Num end),
Cato2=max(case id%4 when 2 then Cato end),
Nam2 =max(case id%4 when 2 then Nam end),
Num2 =max(case id%4 when 2 then Num end),
Cato3=max(case id%4 when 3 then Cato end),
Nam3 =max(case id%4 when 3 then Nam end),
Num3 =max(case id%4 when 3 then Num end),
Cato4=max(case id%4 when 0 then Cato end),
Nam4 =max(case id%4 when 0 then Nam end),
Num4 =max(case id%4 when 0 then Num end)
from
(select *,(select count(*) from @t where Cato<=a.Cato) as id from @t a) b
group by
(b.id-1)/4
order by
(b.id-1)/4/*
Cato1 Nam1 Num1 Cato2 Nam2 Num2 Cato3 Nam3 Num3 Cato4 Nam4 Num4
------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------
A A+ 10 B AA+ 10 C AC+ 10 D A++ 10
E ABC+ 10 F AEF+ 10 NULL NULL NULL NULL NULL NULL
*/
insert into @t select 'A',rtrim('A+ '),10
insert into @t select 'B',rtrim('AA+ '),10
insert into @t select 'C',rtrim('AC+ '),10
insert into @t select 'D',rtrim('A++ '),10
insert into @t select 'E',rtrim('ABC+'),10
insert into @t select 'F',rtrim('AEF+'),10select
Cato1=max(case id%4 when 1 then Cato end),
Nam1 =max(case id%4 when 1 then Nam end),
Num1 =max(case id%4 when 1 then Num end),
Cato2=max(case id%4 when 2 then Cato end),
Nam2 =max(case id%4 when 2 then Nam end),
Num2 =max(case id%4 when 2 then Num end),
Cato3=max(case id%4 when 3 then Cato end),
Nam3 =max(case id%4 when 3 then Nam end),
Num3 =max(case id%4 when 3 then Num end),
Cato4=max(case id%4 when 0 then Cato end),
Nam4 =max(case id%4 when 0 then Nam end),
Num4 =max(case id%4 when 0 then Num end)
from
(select *,(select count(*) from @t where Cato<=a.Cato) as id from @t a) b
group by
(b.id-1)/4
order by
(b.id-1)/4/*
Cato1 Nam1 Num1 Cato2 Nam2 Num2 Cato3 Nam3 Num3 Cato4 Nam4 Num4
------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------
A A+ 10 B AA+ 10 C AC+ 10 D A++ 10
E ABC+ 10 F AEF+ 10 NULL NULL NULL NULL NULL NULL
*/
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货