TAB1Mid
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根据 TAB1 得到 TAB2
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根据 TAB1 得到 TAB2
select mid,rid=row_number() over (partition by mid order by getdate())
into #tb
from tab1declare @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 tab2drop table #tb
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