SQL语句实现分栏的效果

 -->生成测试数据
1

declare @tb table([序号] int,[ID] nvarchar(6),[NAME] nvarchar(13))
Insert @tb
select 1,N'422_01',N'yuechaotian1' union all
select 2,N'422_02',N'yuechaotian2' union all
select 3,N'422_03',N'yuechaotian3' union all
select 4,N'422_04',N'yuechaotian4' union all
select 5,N'422_05',N'yuechaotian5' union all
select 6,N'422_06',N'yuechaotian6' union all
select 7,N'422_07',N'yuechaotian7' union all
select 8,N'422_08',N'yuechaotian8' union all
select 9,N'422_09',N'yuechaotian9' union all
select 10,N'422_10',N'yuechaotian10' union all
select 11,N'422_11',N'yuechaotian11' union all
select 12,N'422_12',N'yuechaotian12' union all
select 13,N'422_13',N'yuechaotian13' union all
select 14,N'422_14',N'yuechaotian14' union all
select 15,N'422_15',N'yuechaotian15' union all
select 16,N'422_16',N'yuechaotian16' union all
select 17,N'422_17',N'yuechaotian17' union all
select 18,N'422_18',N'yuechaotian18'

declare @cnt int
select @cnt = count(1) from @tb
Select
       max(case when cast([序号] as int) <= (case when @cnt <=0 then 1 else @cnt/2 end) then [序号] end) as [序号],
       max(case when cast([序号] as int) <= (case when @cnt <=0 then 1 else @cnt/2 end) then [ID] end) as [ID],
       max(case when cast([序号] as int) <= (case when @cnt <=0 then 1 else @cnt/2 end) then [NAME] end) as [NAME],
       max(case when cast([序号] as int) > (case when @cnt <=0 then 1 else @cnt/2 end) then [序号] end) as [序号] ,
       max(case when cast([序号] as int) > (case when @cnt <=0 then 1 else @cnt/2 end) then [ID] end) as [ID],
       max(case when cast([序号] as int) <= (case when @cnt <=0 then 1 else @cnt/2 end) then [NAME] end) as [NAME]
from @tb
group by (cast([序号] as int)-1)%(case when @cnt <=0 then 1 else @cnt/2 end)
/*
序号          ID     NAME          序号          ID     NAME
----------- ------ ------------- ----------- ------ -------------
1           422_01 yuechaotian1  10          422_10 yuechaotian1
2           422_02 yuechaotian2  11          422_11 yuechaotian2
3           422_03 yuechaotian3  12          422_12 yuechaotian3
4           422_04 yuechaotian4  13          422_13 yuechaotian4
5           422_05 yuechaotian5  14          422_14 yuechaotian5
6           422_06 yuechaotian6  15          422_15 yuechaotian6
7           422_07 yuechaotian7  16          422_16 yuechaotian7
8           422_08 yuechaotian8  17          422_17 yuechaotian8
9           422_09 yuechaotian9  18          422_18 yuechaotian9
*/

 

2

 

declare @t table([序号] int, [ID] varchar(10), [NAME] varchar(20))
insert into @t
select 1, '422_01', 'yuechaotian1' union all
select 2, '422_02', 'yuechaotian2' union all
select 3, '422_03', 'yuechaotian3' union all
select 4, '422_04', 'yuechaotian4' union all
select 5, '422_05', 'yuechaotian5' union all
select 6, '422_06', 'yuechaotian6' union all
select 7, '422_07', 'yuechaotian7' union all
select 8, '422_08', 'yuechaotian8' union all
select 9, '422_09', 'yuechaotian9' union all
select 10, '422_10', 'yuechaotian10' union all
select 11, '422_11', 'yuechaotian11' union all
select 12, '422_12', 'yuechaotian12' union all
select 13, '422_13', 'yuechaotian13' union all
select 14, '422_14', 'yuechaotian14' union all
select 15, '422_15', 'yuechaotian15' union all
select 16, '422_16', 'yuechaotian16' union all
select 17, '422_17', 'yuechaotian17' union all
select 18, '422_18', 'yuechaotian18'

declare @cnt int
select @cnt = count(1) from @t


Select max(case when [序号]<=@cnt/2 then [序号] end)  [序号],
       max(case when [序号]<= @cnt/2  then [ID] end ) [ID],
       max(case when [序号]<= @cnt/2  then [NAME] end ) [NAME],
       max(case when [序号]> @cnt/2  then [序号] end ) [序号2],
       max(case when [序号]> @cnt/2  then [ID] end ) [ID2],
       max(case when [序号]> @cnt/2  then [NAME] end ) [NAME2]
from @t
group by (cast([序号] as int)-1)%(case when @cnt <=0 then 1 else @cnt/2 end)

1    422_01    yuechaotian1    10    422_10    yuechaotian10
2    422_02    yuechaotian2    11    422_11    yuechaotian11
3    422_03    yuechaotian3    12    422_12    yuechaotian12
4    422_04    yuechaotian4    13    422_13    yuechaotian13
5    422_05    yuechaotian5    14    422_14    yuechaotian14
6    422_06    yuechaotian6    15    422_15    yuechaotian15
7    422_07    yuechaotian7    16    422_16    yuechaotian16
8    422_08    yuechaotian8    17    422_17    yuechaotian17
9    422_09    yuechaotian9    18    422_18    yuechaotian18