动态行列转置难死了！
１＼表结构如下：下表中的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,'+@sql+' 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 @sql=@sql+',' +xiangmu from tb group by xiangmuset @sql=stuff(@sql,1,1,'') exec(  @str+@sql+' )) b ')/*neirong    财政                                      党建                                      农村                                      争部---------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------宣传         NULL                                    20.00                                   10.00                                   50.00组织         23.00                                   500.00                                  NULL                                    30.00(2 行受影响)*/