交叉表

lixingkai0 2012-07-11 04:52:43

[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)
有问题,该怎么处理整型字段

...全文

123 5 打赏收藏转发到动态举报

写回复

用AI写文章

5 条回复

切换为时间正序

请发表友善的回复…

发表回复

lixingkai0 2012-07-11

打赏
举报

非常感谢两位,问题解决

SQL77 2012-07-11

打赏
举报

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 @s

exec(@s)

第一个MAX里面有SUM是会有问题的。你自己看着再改。

lixingkai0 2012-07-11

打赏
举报

from (select Cast(Chid As VarChar) As Chid from [3DSource] group by [3DSource].Chid) a
这句的Cast(Chid As VarChar) As Chid好象有问题我不知道怎么处理

天-笑 2012-07-11

打赏
举报





create table [3DSource]

(Team	int		not null,

 Chid	int		not null,

 Dvlue	float	null

)

insert into [3DSource]

select 1,1,23.5 union all

select 2,2,21.2 union all

select 2,3,26.8 union all



declare @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) a

print 'select Team '+@s+ ' from [3DSource] group by Team'



set @s='select Team '+@s+ ' from [3DSource] group by Team'

print @s

exec(@s)

Team        1                      2                      3

----------- ---------------------- ---------------------- ----------------------

1           23.5                   NULL                   NULL

2           NULL                   21.2                   26.8

警告: 聚合或其他 SET 操作消除了空值。



(2 行受影响)

SQL77 2012-07-11

打赏
举报

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 Server 2008 R2数据库系统为例，全面介绍SQL查询技术，包括select语句，Where子句，Order By子句，Group By子句，having语句，TOP和PERCENT限制查询结果，在查询中使用OR和AND运算符，聚合函数应用，多表连接查询，嵌套查询，子查询，联合语句Union，内联接、外联接查询，交叉表查询，视图、存储过程的应用，触发器的应用等。