CREATE PROCEDURE SELECT_TABLE@tablequarter varchar(20),
@tabletype varchar(20),
@tableyear varchar(20)
as
declare @r varchar(200),@r1 varchar(200),@r2 varchar(200)set @r2=stuff((select distinct ',isnull('+QUOTENAME(KPITARGET)+',0) as '+QUOTENAME(KPITARGET) from [PA_TG_Table] where QUARTER = @tablequarter and table_type=@tabletype and table_year = @tableyear),1,1,'' )
set @r1=stuff((select distinct '+isnull('+QUOTENAME(KPITARGET)+',0)' from [PA_TG_Table] where QUARTER = @tablequarter and table_type=@tabletype and table_year = @tableyear),1,1,'' )
set @r=stuff((select distinct ','+QUOTENAME(KPITARGET) from [PA_TG_Table] where QUARTER = @tablequarter and table_type=@tabletype and table_year = @tableyear ),1,1,'')exec ( 'select user_name ,area_name,'+@r1+' as 总分,'+@r2+' from
(select user_name,area_name,KPITARGET,b.[MARK] from [PA_TG_Table] a,[PA_TG_MARK] b where a.[Table_NO]=b.[_id] and table_type='''+@tabletype+''' and qurater='''+@tablequarter+''' and table_year='''+@tableyear+''') a'
+' pivot ( sum([]) for KPITARGET in('+ @r+')) pvt')GO调用的时候exec Select_Table '季度','大区','2010'错误
服务器: 消息 512,级别 16,状态 1,过程 SELECT_TABLE,行 9
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
服务器: 消息 512,级别 16,状态 1,过程 SELECT_TABLE,行 10
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
服务器: 消息 512,级别 16,状态 1,过程 SELECT_TABLE,行 11
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
服务器: 消息 156,级别 15,状态 1,行 1
在关键字 'as' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 2
第 2 行: 'a' 附近有语法错误。
@tabletype varchar(20),
@tableyear varchar(20)
as
declare @r varchar(200),@r1 varchar(200),@r2 varchar(200)set @r2=stuff((select distinct ',isnull('+QUOTENAME(KPITARGET)+',0) as '+QUOTENAME(KPITARGET) from [PA_TG_Table] where QUARTER = @tablequarter and table_type=@tabletype and table_year = @tableyear),1,1,'' )
set @r1=stuff((select distinct '+isnull('+QUOTENAME(KPITARGET)+',0)' from [PA_TG_Table] where QUARTER = @tablequarter and table_type=@tabletype and table_year = @tableyear),1,1,'' )
set @r=stuff((select distinct ','+QUOTENAME(KPITARGET) from [PA_TG_Table] where QUARTER = @tablequarter and table_type=@tabletype and table_year = @tableyear ),1,1,'')exec ( 'select user_name ,area_name,'+@r1+' as 总分,'+@r2+' from
(select user_name,area_name,KPITARGET,b.[MARK] from [PA_TG_Table] a,[PA_TG_MARK] b where a.[Table_NO]=b.[_id] and table_type='''+@tabletype+''' and qurater='''+@tablequarter+''' and table_year='''+@tableyear+''') a'
+' pivot ( sum([]) for KPITARGET in('+ @r+')) pvt')GO调用的时候exec Select_Table '季度','大区','2010'错误
服务器: 消息 512,级别 16,状态 1,过程 SELECT_TABLE,行 9
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
服务器: 消息 512,级别 16,状态 1,过程 SELECT_TABLE,行 10
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
服务器: 消息 512,级别 16,状态 1,过程 SELECT_TABLE,行 11
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
服务器: 消息 156,级别 15,状态 1,行 1
在关键字 'as' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 2
第 2 行: 'a' 附近有语法错误。
CREATE PROCEDURE SELECT_TABLE@tablequarter varchar(20),
@tabletype varchar(20),
@tableyear varchar(20)
as
declare @r varchar(200),@r1 varchar(200),@r2 varchar(200)set @r2=stuff((select distinct ',isnull('+QUOTENAME(KPITARGET)+',0) as '+QUOTENAME(KPITARGET) from [PA_TG_Table] where QUARTER = @tablequarter and table_type=@tabletype and table_year = @tableyear),1,1,'' )
set @r1=stuff((select distinct '+isnull('+QUOTENAME(KPITARGET)+',0)' from [PA_TG_Table] where QUARTER = @tablequarter and table_type=@tabletype and table_year = @tableyear),1,1,'' )
set @r=stuff((select distinct ','+QUOTENAME(KPITARGET) from [PA_TG_Table] where QUARTER = @tablequarter and table_type=@tabletype and table_year = @tableyear ),1,1,'')print ( 'select user_name ,area_name,'+@r1+' as 总分,'+@r2+' from
(select user_name,area_name,KPITARGET,b.[MARK] from [PA_TG_Table] a,[PA_TG_MARK] b where a.[Table_NO]=b.[_id] and table_type='''+@tabletype+''' and qurater='''+@tablequarter+''' and table_year='''+@tableyear+''') a'
+' pivot ( sum([]) for KPITARGET in('+ @r+')) pvt')--exec ( 'select user_name ,area_name,'+@r1+' as 总分,'+@r2+' from
-- (select user_name,area_name,KPITARGET,b.[MARK] from [PA_TG_Table] a,[PA_TG_MARK] b where a.[Table_NO]=b.[_id] and table_type='''+@tabletype+''' and qurater='''+@tablequarter+''' and table_year='''+@tableyear+''') a'
-- +' pivot ( sum([]) for KPITARGET in('+ @r+')) pvt')
先看看,是啥玩意!
set @r1=stuff((select distinct '+isnull('+QUOTENAME(KPITARGET)+',0)' from [PA_TG_Table] where QUARTER = @tablequarter and table_type=@tabletype and table_year = @tableyear for xml path('')),1,1,'' )
set @r=stuff((select distinct ','+QUOTENAME(KPITARGET) from [PA_TG_Table] where QUARTER = @tablequarter and table_type=@tabletype and table_year = @tableyear for xml path('')),1,1,'')
这个方法2005以上的版本
引用 18 楼 ldslove 的回复:
断点测试,一步一步走确认问题在什么地方。快哭了 什么叫断点测试啊把变量直接带入,看看@r,@r1,@r2都输出什么。
能qq远程吗 我找到断点调试那个地方了 下面显示正在运行 上面有个黄色箭头指向set @r2=stuff((select distinct ',isnull('+QUOTENAME(KPITARGET)+',0) as '+QUOTENAME(KPITARGET) from [PA_TG_Table] where QUARTER = @tablequart这一句
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
服务器: 消息 512,级别 16,状态 1,行 8
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
服务器: 消息 512,级别 16,状态 1,行 9
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
第 7 行: 'xml' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 8
第 8 行: 'xml' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 9
第 9 行: 'xml' 附近有语法错误。
@tablequarter varchar(20),
@tabletype varchar(20),
@tableyear varchar(20)
AS
declare @sql varchar(max)
select @sql=isnull(@sql+',','')+'sum(case when a.KPITARGET='''+KPITARGET+''' then 1 else 0 end) as ['+KPITARGET+']'
from(select distinct KPITARGET from [PA_TG_Table] where QUARTER = @tablequarter and table_type=@tabletype and table_year = @tableyear)t
exec ('select [user_name],[area_name],sum(b.[]) 总分,'
+@sql
+' from [PA_TG_Table] a,[PA_TG_MARK] b where a.[Table_NO]=b.[_id] and table_type='''
+@tabletype+''' and qurater='''
+@tablequarter+''' and table_year='''
+@tableyear+''' group [user_name],[area_name]'
)
GO
select [user_name],[area_name],
sum(b.[]) 总分,
sum(case when a.KPITARGET='产品协同' then 1 else 0 end) as [产品协同],
sum(case when a.KPITARGET='货款回收' then 1 else 0 end) as [货款回收],
sum(case when a.KPITARGET='货款损失' then 1 else 0 end) as [货款损失],
sum(case when a.KPITARGET='利润' then 1 else 0 end) as [利润],
sum(case when a.KPITARGET='日常考评' then 1 else 0 end) as [日常考评],
sum(case when a.KPITARGET='细分新市场侵入' then 1 else 0 end) as [细分新市场侵入],
sum(case when a.KPITARGET='销量' then 1 else 0 end) as [销量],
sum(case when a.KPITARGET='新市场开发' then 1 else 0 end) as [新市场开发]
from [PA_TG_Table] a,[PA_TG_MARK] b
where a.[Table_NO]=b.[_id] and table_type='大区' and qurater='季度' and table_year='2010' group by [user_name],[area_name]-----少加一个 by
丢了BY。