原始数据 1列
111111235
111121237
111111239
111112341
111111234
111112236
111111238
111112340
根据后四位拆分成两列
111111234 111111235
111111238 111111239
111112340 111112341
111112236 111112237
111111235
111121237
111111239
111112341
111111234
111112236
111111238
111112340
根据后四位拆分成两列
111111234 111111235
111111238 111111239
111112340 111112341
111112236 111112237
if not object_id(N'Tempdb..#tab') is null
drop table #tab
Go
Create table #tab([id] int)
Insert #tab
select 111111235 union all
select 111112237 union all
select 111111239 union all
select 111112341 union all
select 111111234 union all
select 111112236 union all
select 111111238 union all
select 111112340 UNION ALL
select 111112342 UNION ALL
select 111112344
--测试数据结束--rn奇偶取模得到0或者1
--id这一列用来分组用的
Select id,id%2 AS rn,CASE WHEN id%2=1 THEN id-1 ELSE id END AS gb
INTO #tab2
from #tab
ORDER BY gbSELECT * FROM #tab2--行转列显示
DECLARE @name VARCHAR(max),@sql VARCHAR(max)
set @name =stuff((SELECT DISTINCT ',['+cast(rn AS VARCHAR(20))+']' from #tab2 for xml PATH('')),1,1,'')
set @sql ='SELECT [0],[1] from #tab2 pivot(max(id)for rn in('+@name+'))a'
PRINT @sql
EXEC( @sql)