交叉表，其中一列的行数并不固定
表
fldTeamName   fldName   fldCount
          a                   AA             10
          a                   BB             0
          a                   DD             18


显示结果
fldTeamName     AA     BB     DD
          a               10     0       18

其中   fldName   中的数据不能确定为AA,BB,DD   有可能还会出现CC，EE


------解决方案--------------------
if object_id( 'tbTest ') is not null
drop table tbTest
GO
create table tbTest(fldTeamName varchar(10),fldName varchar(10),fldCount int)
insert tbTest
select 'a ', 'AA ', 10 union all
select 'a ', 'BB ', 0 union all
select 'a ', 'DD ', 18

declare @str varchar(8000)
set @str = ' '
select @str = @str + ',[ ' + fldName + ']=sum(case fldName when ' ' ' + fldName + ' ' ' then fldCount else 0 end) '
from tbTest group by fldName
set @str = 'select fldTeamName '+@str + ' from tbTest group by fldTeamName '
EXEC(@str)

drop table tbTest
/*结果
fldTeamName AA BB DD
------------------------------
a 10 0 18
*/
------解决方案--------------------
这个论坛中例子很多

create table T(fldTeamName varchar(10), fldName varchar(50), fldCount int)
insert T select 'a ', 'AA ',10
union all select 'a ', 'EE ',20
union all select 'a ', 'BB ', 0
union all select 'a ', 'DD ', 18


declare @sql varchar(8000)
set @sql= 'select fldTeamName, '
select @sql=@sql+quotename(fldName)+ '=sum(case when fldName= ' ' '+fldName+ ' ' ' then [fldCount] end), ' from T
group by fldName
select @sql=left(@sql, len(@sql)-1), @sql=@sql+ ' from T group by fldTeamName '
exec(@sql)

drop table t

--
（所影响的行数为 4 行）

fldTeamName AA BB DD EE
----------- ----------- ----------- ----------- -----------
a 10 0 18 20
------解决方案--------------------
CREATE TABLE tb(fldTeamName varchar(10), fldName varchar(10), fldCount int)
INSERT tb SELECT 'a ' , 'AA ' , 10
UNION ALL SELECT 'a ' , 'BB ' , 0
UNION ALL SELECT 'a ' , 'DD ' , 18


DECLARE @s nvarchar(4000)
SET @s= 'SELECT fldTeamName '

SELECT @s=@s
+ ', '+QUOTENAME(fldName)
+N '=SUM(CASE fldName WHEN '+QUOTENAME(fldName,N ' ' ' ')
+N ' THEN fldCount END) '
FROM tb
GROUP BY fldName


EXEC(@s+N '
FROM tb
GROUP BY fldTeamName ')

drop table tb
------解决方案--------------------
declare @str varchar(8000)
set @str = 'select fldTeamName '