create table Tb(line varchar(10),Id varchar(10),input decimal(18,0))
insert Tb(Line,ID,Input)
select 'SB', 'B', 1201
union all select 'SC', 'B' ,19580
union all select 'SC' ,'C', 19
union all select 'SD', 'C' ,10
union all select 'SE' ,'C', 14
union all select 'SB', 'D' ,1683
union all select 'SC' ,'D', 18940
select no=identity(int,1,1),* into #Tb from Tb
--横向显示结果
select line=a.line,id=a.id,input=a.input,line1=b.line,id1=b.id,input1=b.input,line2=c.line,id2=c.id,input2=c.input
from #Tb a left join #Tb b on a.no=b.no-1
left join #Tb c on a.no=c.no-2
where a.no%3=1
drop table #Tb
drop table Tb
-------------结果
line id input line1 id1 input1 line2 id2 input2
---------- ---------- -------------------- ---------- ---------- -------------------- ---------- ---------- --------------------
SB B 1201 SC B 19580 SC C 19
SD C 10 SE C 14 SB D 1683
SC D 18940 NULL NULL NULL NULL NULL NULL
(所影响的行数为 3 行)
--纵向显示结果
--纵向排序
select line=a.line,id=a.id,input=a.input,line1=b.line,id1=b.id,input1=b.input,line2=c.line,id2=c.id,input2=c.input
from #Tb a left join #Tb b on a.no=b.no-(select (count(*)+2)/3 from #Tb)
left join #Tb c on a.no=c.no-2*(select (count(*)+2)/3 from #Tb)
where a.no<=(select (count(*)+2)/3 from #Tb)
--------结果
line id input line1 id1 input1 line2 id2 input2
---------- ---------- -------------------- ---------- ---------- -------------------- ---------- ---------- --------------------
SB B 1201 SD C 10 SC D 18940
SC B 19580 SE C 14 NULL NULL NULL
SC C 19 SB D 1683 NULL NULL NULL
(所影响的行数为 3 行)