交织表

交叉表
[3DSource]表有三个字段Chid为int型
Team, Chid Dvlue
1 1 23.5
2 2 21.2
2 3 26.8
交叉成
Team 1, 2, 3
1 23.5
2 21.2 26.8

declare @s varchar(8000) 
set @s = ' '  
select @s = @s+ ','''+Chid+'''=max(case when Chid= '''+Chid+''' As VarChar then Sum(Dvalue) AS 值 end) ' 
from (select Cast(Chid As VarChar) As Chid from [3DSource] group by [3DSource].Chid) a
--print @s 

set @s='select Team '+@s+ ' from [3DSource] group by Team'
print @s
exec(@s)
有问题,该怎么处理整型字段

[解决办法]
declare @s varchar(8000)
set @s = ' '
select @s = @s+ ','''+Chid+'''=max(case when Chid= '''+LTRIM(Chid)+''' As VarChar then Sum(Dvalue) AS 值 end) '
from (select Cast(Chid As VarChar) As Chid from [3DSource] group by [3DSource].Chid) a
--print @s

set @s='select Team '+@s+ ' from [3DSource] group by Team'
print @s
exec(@s)
转成字符。如果LTRIM不行可以CONVERT强制
[解决办法]

SQL code

create table [3DSource](Team    int        not null, Chid    int        not null, Dvlue    float    null)insert into [3DSource]select 1,1,23.5 union allselect 2,2,21.2 union allselect 2,3,26.8 union alldeclare @s varchar(8000)  set @s = ' '   select @s = @s+ ',['+convert(varchar(8),Chid)+']=Sum(case when Chid= '+convert(varchar(8),Chid)+'  then Dvlue end)  '  from (select Cast(Chid As VarChar) As Chid from [3DSource] group by [3DSource].Chid) aprint 'select Team '+@s+ ' from [3DSource] group by Team'set @s='select Team '+@s+ ' from [3DSource] group by Team'print @sexec(@s)Team        1                      2                      3----------- ---------------------- ---------------------- ----------------------1           23.5                   NULL                   NULL2           NULL                   21.2                   26.8警告: 聚合或其他 SET 操作消除了空值。(2 行受影响)
[解决办法]
SQL code
declare @s varchar(8000)  set @s = ' '   select @s = isnull(@s+ ',','')+' Sum(case when Chid= '''+ltrim(Chid)+''' then (Dvalue) else 0 end) as ['+ltrim(Chid)+']'  from (select Cast(Chid As VarChar(10)) As Chid from [3DSource] group by [3DSource].Chid) a--print @s  set @s='select Team '+@s+ ' from [3DSource] group by Team'print @sexec(@s)