drop table t1
create table t1(id varchar(10),cusno varchar(10),price numeric(10,2))
insert t1
select 'S012','A',35.02
union all
select 'S012','C',38.60
union all
select 'S013','A',52.00Declare @S Varchar(8000)
Select @S='Select ID,'
Select @S=@S + 'SUM(Case Cusno When '''+ Cusno+ ''' Then Price Else 0.00 End) As Price' + Cusno
From t1 Group By Cusno
Select @S = @S + ' From t1 Group By ID '
EXEC(@S)
GO 在查询分析器中,能编译,执行报错
“服务器: 消息 156,级别 15,状态 1,行 1
在关键字 'Case' 附近有语法错误。”
请问错在哪里?
谢谢!
create table t1(id varchar(10),cusno varchar(10),price numeric(10,2))
insert t1
select 'S012','A',35.02
union all
select 'S012','C',38.60
union all
select 'S013','A',52.00Declare @S Varchar(8000)
Select @S='Select ID,'
Select @S=@S + 'SUM(Case Cusno When '''+ Cusno+ ''' Then Price Else 0.00 End) As Price' + Cusno
From t1 Group By Cusno
Select @S = @S + ' From t1 Group By ID '
EXEC(@S)
GO 在查询分析器中,能编译,执行报错
“服务器: 消息 156,级别 15,状态 1,行 1
在关键字 'Case' 附近有语法错误。”
请问错在哪里?
谢谢!
create table t1(id varchar(10),cusno varchar(10),price numeric(10,2))
insert t1
select 'S012','A',35.02
union all
select 'S012','C',38.60
union all
select 'S013','A',52.00Declare @S Varchar(8000)
Select @S='Select ID'
Select @S=@S + ',SUM(Case Cusno When '''+ Cusno+ ''' Then Price Else 0.00 End) As Price' + Cusno
From t1 Group By Cusno
Select @S = @S + ' From t1 Group By ID '
exec(@S)
GO /*ID PriceA PriceC
---------- ---------------------------------------- ----------------------------------------
S012 35.02 38.60
S013 52.00 .00
*/
create table t1(id varchar(10),cusno varchar(10),price numeric(10,2))
insert t1
select 'S012','A',35.02
union all
select 'S012','C',38.60
union all
select 'S013','A',52.00Declare @S Varchar(8000)
Select @S='Select ID'
Select @S=@S + ',SUM(Case Cusno When '''+ Cusno+ ''' Then Price Else 0.00 End) As Price' + Cusno
From t1 Group By Cusno
Select @S = @S + ' From t1 Group By ID '
exec(@S)
GO /*ID PriceA PriceC
---------- ---------------------------------------- ----------------------------------------
S012 35.02 38.60
S013 52.00 .00
*/
你可以这样
print(@S)一看结果 就知道了
set @s='select id'
select @s=@s +',sum(case cusno when '''+cusno+''' then price else 0.00 end) as price '+cusno
from t1 group by cusno
select @s=@s+'from t1 group by id'
exec(@s)为什么执行的时候,还是报错
“服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: 'A' 附近有语法错误。”
不好意思,偶检查了还是不知道错在哪里?
set @s='select id'
select @s=@s +',sum(case cusno when '''+cusno+''' then price else 0.00 end) as price '+cusno
from t1 group by cusno
select @s=@s+' from t1 group by id'
exec (@s)
select @s=@s+' from t1 group by id' 在from 前加一个空格
set @s='select id'
select @s=@s +',sum(case cusno when '''+cusno+''' then price else 0.00 end) as price'+cusno
from t1 group by cusno
select @s=@s+' from t1 group by id'
exec (@s)
1\select @s=@s+ ' from t1 group by id ' 在from 前加一个空格
2\另个select @s=@s + ',sum(case cusno when ' ' '+cusno+ ' ' ' then price else 0.00 end) as price '+cusno as price 后的空格去掉