悟生慧

 

SQL Server表竖列转横向显示

----------联合查询结果集存入临时表

select * into ##tmpa from (select wh_WhName,sum(ps_DiscountMoney) as moneys,psr.wh_Id,Convert(varchar(10),ps_PSRTime,120) as ps_PSRTime
from PosSaleRecord as psr inner join wareHouse as wh on wh.wh_Id=psr.wh_Id group by psr.wh_Id,wh_WhName,Convert(varchar(10),ps_PSRTime,120)) a

 ----------把临时表竖列转换横向显示

declare @sql varchar(8000)         --将竖列横向显示
set @sql = 'select ps_PSRTime '
select @sql = @sql + ' , (case wh_whname when ''' + wh_whname + ''' then moneys else 0 end) [' + wh_whname + ']'
from (
select distinct wh_whname from ##tmpa
) as a
set @sql = @sql + ' into ##tmpPPPt from ##tmpa'
exec(@sql)

----------查询临时表

select * from ##tmpa   
select * from ##tmpPPPt

----------删除临时表
drop table ##tmpPPPt
drop table ##tmpa

posted on 2011-06-02 11:55  悟生慧  阅读(2235)  评论(0编辑  收藏  举报

导航