SQL 中交叉表无法插入字符型数值

Question

太长,参考如下:
/*
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华 
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)： 
姓名 语文 数学 物理 
---- ---- ---- ----
李四 74   84   94
张三 74   83   93
-------------------
*/create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')---------------------------------/*
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名 语文 数学 物理 平均分 总分 
---- ---- ---- ---- ------ ----
李四 74   84   94   84.00  252
张三 74   83   93   83.33  250
*/--SQL SERVER 2000 静态SQL。
select 姓名 姓名,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理,
  cast(avg(分数*1.0) as decimal(18,2)) 平均分,
  sum(分数) 总分
from tb
group by 姓名--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m , 
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')drop table tb ------------------
------------------/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名 语文 数学 物理
张三 74　　83　　93
李四 74　　84　　94
想变成(得到如下结果)： 
姓名 课程 分数 
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go--SQL SERVER 2000 静态SQL。
select * from
(
 select 姓名 , 课程 = '语文' , 分数 = 语文 from tb 
 union all
 select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
 union all
 select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns 
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。--------------------
/*
问题：在上述的结果上加个平均分，总分，得到如下结果：
姓名 课程   分数
---- ------ ------
李四 语文   74.00
李四 数学   84.00
李四 物理   94.00
李四 平均分 84.00
李四 总分   252.00
张三 语文   74.00
张三 数学   83.00
张三 物理   93.00
张三 平均分 83.33
张三 总分   250.00
------------------
*/select * from
(
 select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb 
 union all
 select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
 union all
 select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
 union all
 select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
 union all
 select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 enddrop table tb

Answer 1

对于不能使用SUM合计的字符型字段,尝试使用MAX.

Answer 2

如楼主所述：车牌貌似AA256。
select @sql = @sql + ',isnull(sum(case acctname  when N'''+acctname+''' then jine else 0  end),0)as ['+acctname+']' + ',isnull(sum(case acctname  when N'''+acctname+''' then chepai else 0  end),0)as  车牌' 
注意两个红色部分，第一个jine定义为numeric(18,4)，操作没有问题。
第二个chepai varchar(50)，而其内容为‘AA256’，无法强制转换为Int类型。

Answer 3

 --示例数据:
CREATE TABLE tb(ID varchar(10),NGNO char(1),QTY int)
INSERT tb SELECT '200505','A',10
UNION ALL SELECT '200505','B',20
UNION ALL SELECT '200505','C',30
UNION ALL SELECT '200506','B',10
UNION ALL SELECT '200506','C',20
UNION ALL SELECT '200506','D',30
UNION ALL SELECT '200506','E',40
GO--交叉数据报表要求1:
--  按ID列分组，将NGNO列水平显示为多列，但每列由NGNO的大小顺序决定，而不是由NGNO的值决定，对于示例数据，最终的结果要求如下：--ID         col1    col2    col3    col4
---------- ------- ------- ------- -------
--200505     A(10)   B(20)   C(30)   
--200506     B(10)   C(20)   D(30)   E(40)--实现代码
DECLARE @i varchar(10),@s nvarchar(4000)
SELECT TOP 1 @s='',@I=COUNT(*) FROM tb
GROUP BY ID
ORDER BY COUNT(*) DESC
WHILE @i>0
SELECT @s=N',col'+@i
        +N'=ISNULL(MAX(CASE GID WHEN '+@i
        +N' THEN QTY END),'''')'+@s,
    @i=@i-1
EXEC(N'SELECT ID'+@s+N'
FROM(
    SELECT ID,QTY=RTRIM(NGNO)+QUOTENAME(QTY,N''()''),
    GID=(SELECT COUNT(DISTINCT NGNO) FROM TB WHERE ID=a.ID AND NGNO<=a.NGNO)
    FROM tb a
)a GROUP BY ID')
GO--交叉数据报表要求2:
--    按ID列分组，将NGNO及QTY列合并显示在一个列中，最终的结果要求如下： --ID         Value
---------- -----------------------------------------------
--200505     A(10)        B(20)        C(30)
--200506     B(10)        C(20)        D(30)        E(40)（所影响的行数为 2 行）--实现处理的函数
CREATE FUNCTION f_Str(@ID varchar(10))
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @r varchar(8000)
    SET @r=''
    SELECT @r=@r+SPACE(8)+RTRIM(NGNO)+QUOTENAME(QTY,'()')
    FROM tb
    WHERE ID=@ID
    RETURN(STUFF(@r,1,8,''))
END
GO--调用实现查询
SELECT ID,Value=dbo.f_Str(ID) FROM tb GROUP BY IDdrop table tb
drop function f_str
GO 
--行列互换/*--有表
indust     200301     200302     200303     
---------- ---------- ---------- ---------- 
a          111        222        333
b          444        555        666
c          777        888        999
d          789        910        012
--要求得到结果
日期     a    b    c    d    
------ ---- ---- ---- ---- 
200301 111  444  777  789
200302 222  555  888  910
200303 333  666  999  012
--*/--创建测试表
create table test(indust varchar(10)
    ,[200301] varchar(10)
    ,[200302] varchar(10)
    ,[200303] varchar(10))
insert test select 'a','111','222','333'
union all select 'b','444','555','666' 
union all select 'c','777','888','999'
union all select 'd','789','910','012'
go--数据处理
declare @f1 varchar(8000),@f2 varchar(8000),@f3 varchar(8000)
select @f1='',@f2='',@f3=''
select @f1=@f1+',['+indust+']='''+[200301]+''''
    ,@f2=@f2+','''+[200302]+''''
    ,@f3=@f3+','''+[200303]+''''
from test
exec('select 日期=''200301'''+@f1
    +' union all select ''200302'''+@f2
    +' union all select ''200303'''+@f3)
go
--删除测试表
select * from test
drop table test/*--测试结果
日期     a    b    c    d    
------ ---- ---- ---- ---- 
200301 111  444  777  789
200302 222  555  888  910
200303 333  666  999  012
--*/

Answer 4

我并没有对 chepai 这个字段进行函数运算

Answer 5

case acctname  when N'''+acctname+''' then chepai else 0  end
改成
case acctname  when N'''+acctname+''' then chepai else ''''  end

Answer 6

发表于：2008-04-18 08:52:055楼 得分:0 
如楼主所述：车牌貌似AA256。 
select @sql = @sql + ',isnull(sum(case acctname  when N'''+acctname+''' then jine else 0  end),0)as ['+acctname+']' + ',isnull(sum(case acctname  when N'''+acctname+''' then chepai else 0  end),0)as  车牌'  
注意两个红色部分，第一个jine定义为numeric(18,4)，操作没有问题。 
第二个chepai varchar(50)，而其内容为‘AA256’，无法强制转换为Int类型。 
  
我想解决的就是这个问题

Answer 7

不明白这句要返回什么：isnull(sum(case acctname  when N'''+acctname+''' then chepai else 0  end),0)as  车牌'依语句，应返回Int型，而楼主的对话中似乎要返回varchar？

Answer 8

 我是要返回varchar没错,请问如何修改?

Answer 9

看看下面2种是不是你需要的create table feiyong(acctname varchar(100),people varchar(20),jine numeric(18,4),chepai varchar(50)) 
insert into feiyong(acctname,people,jine,chepai)  values('汽车费用A','A',100,'AA256')
insert into feiyong(acctname,people,jine,chepai)  values('汽车费用A','B',200,'AA253')
insert into feiyong(acctname,people,jine,chepai)  values('汽车费用B','B',300,'AA253')
insert into feiyong(acctname,people,jine,chepai)  values('汽车费用C','A',50,'AA256')
insert into feiyong(acctname,people,jine,chepai)  values('汽车费用A','A',150,'AA257')
godeclare @sql  varchar(8000) 
set @sql = 'select people,chepai ' 
select @sql = @sql + ',isnull(sum(case acctname  when N'''+acctname+''' then jine else 0  end),0)as ['+acctname+']' 
 from (select distinct acctname from feiyong) as a 
select @sql = @sql+' from feiyong  group by people,chepai ' 
exec(@sql) 
/*返回结果
people   chepai   汽车费用A    汽车费用B     汽车费用C
-------- -------- ----------- -------------- ---------
B        AA253    200.0000    300.0000       0.0000
A        AA256    100.0000    0.0000         50.0000
A        AA257    150.0000    0.0000         0.0000*/
--如果你要将A的两个车牌统计为一个,需要函数
create function getstr(@people varchar(20))
returns varchar(2000)
as 
begin
   declare @str varchar(2000)
   set @str=''
   select @str=@str+','+rtrim(chepai) from feiyong where people=@people group by chepai
   select @str=right(@str,len(@str)-1)
   return @str
end
godeclare @sql2  varchar(8000) 
set @sql2 = 'select people ' 
select @sql2 = @sql2 + ',isnull(sum(case acctname  when N'''+acctname+''' then jine else 0  end),0)as ['+acctname+']' 
                   --+ ',isnull(sum(case acctname  when N'''+acctname+''' then chepai else 0  end),0)as  车牌' 
 from (select distinct acctname from feiyong) as a 
select @sql2 = @sql2+',dbo.getstr(people) as 车牌 from feiyong  group by people ' 
exec(@sql2) /*返回结果
people 汽车费用A  汽车费用B     汽车费用C        车牌
------ --------- ------------- ----------------- ----------------
A      250.0000  0.0000        50.0000           AA256,AA257
B      200.0000  300.0000      0.0000            AA253*/

Answer 10

改成这样,还是不行 
select @sql = @sql + ',isnull((case acctname  when N'''+acctname+''' then jine else 0  end),0)as ['+acctname+']' + ',isnull((case acctname  when N'''+acctname+''' then chepai else 0  end),0)as  车牌' 
 from (select distinct acctname from feiyong) as a 
select @sql = @sql+' from feiyong  ' 
exec(@sql) 
drop table feiyong 
GO 

Answer 11

改成这样,还是不行 
select @sql = @sql + ',isnull((case acctname  when N'''+acctname+''' then jine else 0  end),0)as ['+acctname+']' + ',isnull((case acctname  when N'''+acctname+''' then chepai else 0  end),0)as  车牌' 
 from (select distinct acctname from feiyong) as a 
select @sql = @sql+' from feiyong  ' 
exec(@sql) 
drop table feiyong 
GO