select '20110901' as date,page_id,count(1) as pv into #temptable from ui_visit_20110901 where page_id='102001' or page_id='103001' or page_id='104001' group by page_id
union all
select '20110902' as date,page_id,count(1) as pv from ui_visit_20110902 where page_id='102001' or page_id='103001' or page_id='104001' group by page_id
select date,
max(case when page_id=103001 then pv
end) as '103001_pv',
max(case when page_id=102001 then pv
end) as '103002_pv',
max(case when page_id=104001 then pv
end) as '103003_pv'
from #temptable group by date
行列转换思路