34,597
社区成员
发帖
与我相关
我的任务
分享
TAB1
Mid
1
2
1
3
1
1
3
4
------------------------------------
TAB2
m1 m2 m3 m4
1 1 1 1
2 null null null
3 3 null null
4 null null null
select
m1=max(case when rn%4=1 then mid end),
m2=max(case when rn%4=2 then mid end),
m3=max(case when rn%4=3 then mid end),
m4=max(case when rn%4=0 then mid end)
from
(select rn=row_number() over(order by getdate()),* from tab1) t
group by
(rn-1)/4
select mid,rid=row_number() over (partition by mid order by getdate())
into #tb
from tab1
declare @sql varchar(8000)
select @sql = isnull(@sql+',','')+',max(case rid when '+ltrim(rid)+' then mid else null end) [m'+ltrim(rid)+']'
from #tb
group by rid
order by rid
select @sql = @sql + ' into tab2 from #tb group by rid'
exec(@sql)
select * from tab2
drop table #tb