按照ID group by 即可, 也可以动态实现, 在论坛里面找找就可以了, 很多的, 这个是静态的,-->生成测试数据
declare @tb table([id] nvarchar(2),[name] nvarchar(4)) Insert @tb select N'01',N'jon' union all select N'02',N'jie' union all select N'03',N'jekc' union all select N'04',N'lli' union all select N'05',N'yang' union all select N'06',N'fan' declare @cnt int select @cnt = count(1) from @tb Select max(case when cast(id as int) <= (case when @cnt <=0 then 1 else @cnt/2 end) then id end) as id, max(case when cast(id as int) <= (case when @cnt <=0 then 1 else @cnt/2 end) then [name] end) as [name], max(case when cast(id as int) > (case when @cnt <=0 then 1 else @cnt/2 end) then id end) as id , max(case when cast(id as int) > (case when @cnt <=0 then 1 else @cnt/2 end) then [name] end) as [name] from @tb group by (cast(id as int)-1)%(case when @cnt <=0 then 1 else @cnt/2 end) /* id name id name ---- ---- ---- ---- 01 jon 04 lli 02 jie 05 yang 03 jekc 06 fan */
对查出的结果进行二次查询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' Select max(case ([序号]-1)%2 when 0 then [序号] end ) [序号], max(case ([序号]-1)%2 when 0 then [ID] end ) [ID], max(case ([序号]-1)%2 when 0 then [NAME] end ) [NAME], max(case ([序号]-1)%2 when 1 then [序号] end ) [序号2], max(case ([序号]-1)%2 when 1 then [ID] end ) [ID2], max(case ([序号]-1)%2 when 1 then [NAME] end ) [NAME2] from @t group by ([序号]-1)/2 /* 序号 ID NAME 序号2 ID2 NAME2 1 422_01 yuechaotian1 2 422_02 yuechaotian2 3 422_03 yuechaotian3 4 422_04 yuechaotian4 5 422_05 yuechaotian5 6 422_06 yuechaotian6 7 422_07 yuechaotian7 8 422_08 yuechaotian8 9 422_09 yuechaotian9 10 422_10 yuechaotian10 11 422_11 yuechaotian11 12 422_12 yuechaotian12 13 422_13 yuechaotian13 14 422_14 yuechaotian14 15 422_15 yuechaotian15 16 422_16 yuechaotian16 17 422_17 yuechaotian17 18 422_18 yuechaotian18 */
-->生成测试数据
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 */
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
也可以动态实现,
在论坛里面找找就可以了,
很多的,
这个是静态的,-->生成测试数据
declare @tb table([id] nvarchar(2),[name] nvarchar(4))
Insert @tb
select N'01',N'jon' union all
select N'02',N'jie' union all
select N'03',N'jekc' union all
select N'04',N'lli' union all
select N'05',N'yang' union all
select N'06',N'fan'
declare @cnt int
select @cnt = count(1) from @tb
Select
max(case when cast(id as int) <= (case when @cnt <=0 then 1 else @cnt/2 end) then id end) as id,
max(case when cast(id as int) <= (case when @cnt <=0 then 1 else @cnt/2 end) then [name] end) as [name],
max(case when cast(id as int) > (case when @cnt <=0 then 1 else @cnt/2 end) then id end) as id ,
max(case when cast(id as int) > (case when @cnt <=0 then 1 else @cnt/2 end) then [name] end) as [name]
from @tb
group by (cast(id as int)-1)%(case when @cnt <=0 then 1 else @cnt/2 end)
/*
id name id name
---- ---- ---- ----
01 jon 04 lli
02 jie 05 yang
03 jekc 06 fan
*/
序号 ID NAME------- --------- ------------- 1 422_01 yuechaotian1 2 422_02 yuechaotian2 3 422_03 yuechaotian3 4 422_04 yuechaotian4 5 422_05 yuechaotian5 6 422_06 yuechaotian6 7 422_07 yuechaotian7 8 422_08 yuechaotian8 9 422_09 yuechaotian9 10 422_10 yuechaotian10 11 422_11 yuechaotian11 12 422_12 yuechaotian12 13 422_13 yuechaotian13 14 422_14 yuechaotian14 15 422_15 yuechaotian15 16 422_16 yuechaotian16 17 422_17 yuechaotian17 18 422_18 yuechaotian18现在要改成两栏的,修改后效果如下序号 ID NAME 序号 ID NAME
--- ---------- -------------- ---- --------- -------------
1 422_01 yuechaotian01 10 422_10 yuechaotian10
2 422_02 yuechaotian02 11 422_11 yuechaotian11
3 422_03 yuechaotian03 12 422_12 yuechaotian12
4 422_04 yuechaotian04 13 422_13 yuechaotian13
5 422_05 yuechaotian05 14 422_14 yuechaotian14
6 422_06 yuechaotian06 15 422_15 yuechaotian15
7 422_07 yuechaotian07 16 422_16 yuechaotian16
8 422_08 yuechaotian08 17 422_17 yuechaotian17
9 422_09 yuechaotian09 18 422_18 yuechaotian18
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' Select max(case ([序号]-1)%2 when 0 then [序号] end ) [序号],
max(case ([序号]-1)%2 when 0 then [ID] end ) [ID],
max(case ([序号]-1)%2 when 0 then [NAME] end ) [NAME],
max(case ([序号]-1)%2 when 1 then [序号] end ) [序号2],
max(case ([序号]-1)%2 when 1 then [ID] end ) [ID2],
max(case ([序号]-1)%2 when 1 then [NAME] end ) [NAME2]
from @t
group by ([序号]-1)/2
/*
序号 ID NAME 序号2 ID2 NAME2
1 422_01 yuechaotian1 2 422_02 yuechaotian2
3 422_03 yuechaotian3 4 422_04 yuechaotian4
5 422_05 yuechaotian5 6 422_06 yuechaotian6
7 422_07 yuechaotian7 8 422_08 yuechaotian8
9 422_09 yuechaotian9 10 422_10 yuechaotian10
11 422_11 yuechaotian11 12 422_12 yuechaotian12
13 422_13 yuechaotian13 14 422_14 yuechaotian14
15 422_15 yuechaotian15 16 422_16 yuechaotian16
17 422_17 yuechaotian17 18 422_18 yuechaotian18
*/
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
*/
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
----------- ------ ------------- ----------- ------ -------------
1 422_01 yuechaotian1 17 422_17 yuechaotian9
2 422_02 yuechaotian2 18 422_18 yuechaotian18
3 422_03 yuechaotian3 NULL NULL NULL
4 422_04 yuechaotian4 NULL NULL NULL
5 422_05 yuechaotian5 13 422_13 yuechaotian13
6 422_06 yuechaotian6 14 422_14 yuechaotian14
7 422_07 yuechaotian7 15 422_15 yuechaotian15
8 422_08 yuechaotian8 16 422_16 yuechaotian8我删除的是第11和12行记录,但是第9和10也显示不出来了