１＼表结构如下：下表中的xiangmu不止这四项，总共有20多个吧，neirong也不止这两项，有10多项吧，想要实现行列转置．
NeiRong xiangmu score
组织 财政 23.00
组织 争部 30.00
宣传 争部 50.00
宣传 农村 10.00
组织 党建 500.00
宣传 党建 20.00
效果如下：
-- 财政 争部 农村 党建 --这总共20多项
--组织 23 30 0 500
--宣传 0 50 10 20
--
--
--
--
------解决方案--------------------
- SQL code
---测试数据---if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([NeiRong] varchar(4),[xiangmu] varchar(4),[score] numeric(5,2))insert [tb]select '组织','财政',23.00 union allselect '组织','争部',30.00 union allselect '宣传','争部',50.00 union allselect '宣传','农村',10.00 union allselect '组织','党建',500.00 union allselect '宣传','党建',20.00 ---查询---declare @sql varchar(8000)select @sql=isnull(@sql+',','') +'sum(case when xiangmu='''+xiangmu+''' then score else 0 end) as ['+xiangmu+']'from(select distinct xiangmu from tb) texec ('select NeiRong,[email protected]+' from tb group by NeiRong')---结果---NeiRong 财政 党建 农村 争部------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------宣传 0.00 20.00 10.00 50.00组织 23.00 500.00 0.00 30.00(2 行受影响)
------解决方案--------------------
- SQL code
if object_id('tb')is not null drop table tbgocreate table tb (neirong nvarchar(10),xiangmu nvarchar(10),socre decimal(10,2))goinsert into tb select '组织','财政',23.00 union all select '组织','争部',30.00 union all select '宣传','争部',50.00 union all select '宣传','农村',10.00 union all select '组织','党建',500.00 union all select '宣传','党建',20.00declare @str nvarchar(4000),@sql nvarchar(400)set @str='select * from tb a pivot (sum(socre) for xiangmu in ( 'set @sql=''select @[email protected]+',' +xiangmu from tb group by xiangmuset @sql=stuff(@sql,1,1,'') exec( @[email protected]+' )) b ')/*neirong 财政 党建 农村 争部---------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------宣传 NULL 20.00 10.00 50.00组织 23.00 500.00 NULL 30.00(2 行受影响)*/