--创建数据表
CREATE TABLE [dbo].[Tb1](
    [AA] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
    [BB] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
    [CC] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
--插入数据值
insert into Tb1(AA,BB,CC) values('王1','A1','C1')
insert into Tb1(AA,BB,CC) values('王2','A2','C2')
insert into Tb1(AA,BB,CC) values('王3','A3','C3')
insert into Tb1(AA,BB,CC) values('王4','A4','C4')
insert into Tb1(AA,BB,CC) values('王5','A5','C5')
insert into Tb1(AA,BB,CC) values('王6','A6','C6')
insert into Tb1(AA,BB,CC) values('王7','A7','C7')
insert into Tb1(AA,BB,CC) values('王8','A8','C8')
insert into Tb1(AA,BB,CC) values('王9','A9','C9')
insert into Tb1(AA,BB,CC) values('王10','A10','C10')
insert into Tb1(AA,BB,CC) values('王11','A11','C11')
insert into Tb1(AA,BB,CC) values('王12','A12','C12')
insert into Tb1(AA,BB,CC) values('王13','A13','C13')

CREATE TABLE [dbo].[Tb1](
    [AA] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
    [BB] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
    [CC] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
--插入数据值
insert into Tb1(AA,BB,CC) values('王01','A1','C1')
insert into Tb1(AA,BB,CC) values('王02','A2','C2')
insert into Tb1(AA,BB,CC) values('王03','A3','C3')
insert into Tb1(AA,BB,CC) values('王04','A4','C4')
insert into Tb1(AA,BB,CC) values('王05','A5','C5')
insert into Tb1(AA,BB,CC) values('王06','A6','C6')
insert into Tb1(AA,BB,CC) values('王07','A7','C7')
insert into Tb1(AA,BB,CC) values('王08','A8','C8')
insert into Tb1(AA,BB,CC) values('王09','A9','C9')
insert into Tb1(AA,BB,CC) values('王10','A10','C10')
insert into Tb1(AA,BB,CC) values('王11','A11','C11')
insert into Tb1(AA,BB,CC) values('王12','A12','C12')
insert into Tb1(AA,BB,CC) values('王13','A13','C13')
insert into Tb1(AA,BB,CC) values('王14','A14','C14')
go

declare @i as int
set @i = 2 --这里更改值，可变换显示列数

declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ',max(case (px-1)/n.cnt when ' + cast(i as varchar) + ' then AA else '''' end) [AA' + cast(i+1 as varchar) + ']'
                   + ',max(case (px-1)/n.cnt when ' + cast(i as varchar) + ' then BB else '''' end) [BB' + cast(i+1 as varchar) + ']'
                   + ',max(case (px-1)/n.cnt when ' + cast(i as varchar) + ' then CC else '''' end) [CC' + cast(i+1 as varchar) + ']'
from (select number i from master..spt_values where type='p' and number < @i) as a
set @sql = 'select ' + substring(@sql,2,len(@sql)) + ' from (
  select t.* , px = (select count(1) from Tb1 where aa < t.aa) + 1 From Tb1 t
) m ,(select (case when count(1)%'+cast(@i as varchar)+' = 0 then count(1)/'+cast(@i as varchar)+' else count(1)/'+cast(@i as varchar)+'+1 end) cnt from Tb1) n
group by (px-1)%n.cnt
'
exec(@sql) 

drop table Tb1

/*
AA1        BB1        CC1        AA2        BB2        CC2        
---------- ---------- ---------- ---------- ---------- ---------- 
王01        A1         C1         王08        A8         C8        
王02        A2         C2         王09        A9         C9        
王03        A3         C3         王10        A10        C10       
王04        A4         C4         王11        A11        C11       
王05        A5         C5         王12        A12        C12       
王06        A6         C6         王13        A13        C13       
王07        A7         C7         王14        A14        C14       


*/