行列转换报语法错误

z418841875 2015-07-24 11:52:19



ALTER procedure [dbo].[acmor_cost] @YYMM varchar(6) 

as

	if not exists(select * from sys.objects where name='TB_cost')

	begin

		create table TB_cost

		(

			成本归属 varchar(50),类型 varchar(10),人数 int,加班费 float,应付小计 float,附加成本合计 float

		)

	end

truncate table TB_cost



insert into TB_cost select(case  when c.Myfield1='01' then 'B24' when c.Myfield1='05' then 'X241'

when c.Myfield1='06' then '其他' when c.Myfield1='15' then '办公人员'

when c.Myfield1='17' then '铣床' when c.Myfield1='18' then 'TMA'

when c.Myfield1='19' then '其他/保洁员' when c.Myfield1='20' then 'N12'

when c.Myfield1='21' then 'X340' when c.Myfield1='22' then 'B9'

when c.Myfield1='23' then 'N56/N61' when c.Myfield1='24' then 'B170'

when c.Myfield1='09' then 'X241-Assy' when  c.Myfield1='11' then 'X241-Shaft'

when c.Myfield1='13' then 'X241-Ring' when c.Myfield1='14' then 'X241-Nut'

when c.Myfield1='12' then 'X241-Collar' when c.Myfield1='25' then 'B171' end),EmpTypeName,

count(*) as 人数,SUM(PayNo10) as 加班费,

SUM(PayNo20) as 应付,SUM(PayNo21+PayNo22+PayNo23+PayNo24+PayNo29) as 成本

from PayMonthData a left join AttMonthData b 

left join PerEmployee c on b.EmpID=c.EmpID

on a.EmpID=b.EmpID and a.YYMM=b.YYMM

where a.YYMM=@YYMM and c.Myfield1!='' and c.Myfield1 is not null

and EmpTypeName!='' and EmpTypeName is not null

and UserTypeName!='' and UserTypeName is not null

group by c.Myfield1,EmpTypeName,UserTypeName



declare @sql varchar(8000)



set @sql=''



select @sql=@sql+','+ 成本归属 from TB_cost group by 成本归属

print(@sql)

set @sql=STUFF(@sql,1,1,'')

print(@sql)

set @sql='select * from TB_cost pivot(EmpTypeName,count(*),SUM(PayNo10),SUM(PayNo20),SUM(PayNo21+PayNo22+PayNo23+PayNo24+PayNo29) for 成本归属 in ('+@sql+')) t'



print(@sql)

exec(@sql)





---exec [dbo].[acmor_cost] '201506'

运行结果
,B170,B171,B24,B9,N12,N56/N61,TMA,X241,X340,办公人员,其他,其他/保洁员,铣床
B170,B171,B24,B9,N12,N56/N61,TMA,X241,X340,办公人员,其他,其他/保洁员,铣床
select * from TB_cost pivot(EmpTypeName,count(*),SUM(PayNo10),SUM(PayNo20),SUM(PayNo21+PayNo22+PayNo23+PayNo24+PayNo29) for 成本归属 in (B170,B171,B24,B9,N12,N56/N61,TMA,X241,X340,办公人员,其他,其他/保洁员,铣床)) t
消息 102，级别 15，状态 1，第 1 行
',' 附近有语法错误。

sos

...全文

206 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

z418841875 2015-07-28

打赏
举报

引用 5 楼 xxfvba 的回复:

Pivot后只能接一个聚合函数吧

ALTER procedure [dbo].[amcor_cost] @YYMM varchar(6) as if not exists(select * from sys.objects where name='TB_cost') begin create table TB_cost ( 成本归属 varchar(50),类型 varchar(10),人数 int,加班费 float,应付小计 float,附加成本合计 float ) end truncate table TB_cost insert into TB_cost select(case when c.Myfield1='01' then 'B24' when c.Myfield1='05' then 'X241' when c.Myfield1='06' then '其他' when c.Myfield1='15' then '办公人员' when c.Myfield1='17' then '铣床' when c.Myfield1='18' then 'TMA' when c.Myfield1='19' then '其他保洁员' when c.Myfield1='20' then 'N12' when c.Myfield1='21' then 'X340' when c.Myfield1='22' then 'B9' when c.Myfield1='23' then 'N56N61' when c.Myfield1='24' then 'B170' when c.Myfield1='09' then 'X241-Assy' when c.Myfield1='11' then 'X241-Shaft' when c.Myfield1='13' then 'X241-Ring' when c.Myfield1='14' then 'X241-Nut' when c.Myfield1='12' then 'X241-Collar' when c.Myfield1='25' then 'B171' end),EmpTypeName, count(*) as 人数,SUM(PayNo10) as 加班费, SUM(PayNo20) as 应付,SUM(PayNo21+PayNo22+PayNo23+PayNo24+PayNo29) as 成本 from PayMonthData a left join AttMonthData b left join PerEmployee c on b.EmpID=c.EmpID on a.EmpID=b.EmpID and a.YYMM=b.YYMM where a.YYMM=@YYMM and c.Myfield1!='' and c.Myfield1 is not null and EmpTypeName!='' and EmpTypeName is not null and UserTypeName!='' and UserTypeName is not null group by c.Myfield1,EmpTypeName order by c.myfield1 declare @sql varchar(8000) set @sql='' select @sql=@sql+','+成本归属 from TB_cost group by 成本归属 print(@sql) set @sql=STUFF(@sql,1,1,'') print(@sql) set @sql='select * from TB_cost pivot(SUM(PayNo20) for 成本归属 in ('+@sql+'))' print(@sql) exec(@sql) (21 行受影响) ,B170,B171,B24,B9,N12,N56N61,X241,X340,办公人员,其他,其他保洁员,铣床 B170,B171,B24,B9,N12,N56N61,X241,X340,办公人员,其他,其他保洁员,铣床 select * from TB_cost pivot(SUM(PayNo20) for 成本归属 in (B170,B171,B24,B9,N12,N56N61,X241,X340,办公人员,其他,其他保洁员,铣床)) 消息 102，级别 15，状态 1，第 1 行 ')' 附近有语法错误。

xxfvba 2015-07-27

打赏
举报

Pivot后只能接一个聚合函数吧

许晨旭 2015-07-27

打赏
举报

把这个改一下

declare @sql varchar(8000)
 
set @sql=''
 
select @sql=ISNULL(@sql+',','')+ quotename(成本归属) from TB_cost group by 成本归属

z418841875 2015-07-25

打赏
举报

引用 2 楼 Landa_Ran 的回复:

pivot 函数里能用这么多聚合吗？，你先把SQL语句print出来执行一下，看看哪里报错


ALTER procedure [dbo].[acmor_cost] @YYMM varchar(6) 
as
	if not exists(select * from sys.objects where name='TB_cost')
	begin
		create table TB_cost
		(
			成本归属 varchar(50),类型 varchar(10),人数 int,加班费 float,应付小计 float,附加成本合计 float
		)
	end
truncate table TB_cost

insert into TB_cost select(case  when c.Myfield1='01' then 'B24' when c.Myfield1='05' then 'X241'
when c.Myfield1='06' then '其他' when c.Myfield1='15' then '办公人员'
when c.Myfield1='17' then '铣床' when c.Myfield1='18' then 'TMA'
when c.Myfield1='19' then '其他/保洁员' when c.Myfield1='20' then 'N12'
when c.Myfield1='21' then 'X340' when c.Myfield1='22' then 'B9'
when c.Myfield1='23' then 'N56/N61' when c.Myfield1='24' then 'B170'
when c.Myfield1='09' then 'X241-Assy' when  c.Myfield1='11' then 'X241-Shaft'
when c.Myfield1='13' then 'X241-Ring' when c.Myfield1='14' then 'X241-Nut'
when c.Myfield1='12' then 'X241-Collar' when c.Myfield1='25' then 'B171' end),EmpTypeName,
count(*) as 人数,SUM(PayNo10) as 加班费,
SUM(PayNo20) as 应付,SUM(PayNo21+PayNo22+PayNo23+PayNo24+PayNo29) as 成本
from PayMonthData a left join AttMonthData b 
left join PerEmployee c on b.EmpID=c.EmpID
on a.EmpID=b.EmpID and a.YYMM=b.YYMM
where a.YYMM=@YYMM and c.Myfield1!='' and c.Myfield1 is not null
and EmpTypeName!='' and EmpTypeName is not null
and UserTypeName!='' and UserTypeName is not null
group by c.Myfield1,EmpTypeName,UserTypeName

declare @sql varchar(8000)

set @sql=''

select @sql=@sql+',['+ 成本归属+']' from TB_cost group by 成本归属
print(@sql)
set @sql=STUFF(@sql,1,1,'')
print(@sql)
set @sql='select * 
from TB_cost 
pivot(EmpTypeName,count(*),SUM(PayNo10),SUM(PayNo20),SUM(PayNo21+PayNo22+PayNo23+PayNo24+PayNo29) for 成本归属 in ('+@sql+')) t'

print(@sql)
exec(@sql)


---exec [dbo].[acmor_cost] '201506'

我结果贴出来了

许晨旭 2015-07-24

打赏
举报

pivot 函数里能用这么多聚合吗？，你先把SQL语句print出来执行一下，看看哪里报错


ALTER procedure [dbo].[acmor_cost] @YYMM varchar(6) 
as
	if not exists(select * from sys.objects where name='TB_cost')
	begin
		create table TB_cost
		(
			成本归属 varchar(50),类型 varchar(10),人数 int,加班费 float,应付小计 float,附加成本合计 float
		)
	end
truncate table TB_cost

insert into TB_cost select(case  when c.Myfield1='01' then 'B24' when c.Myfield1='05' then 'X241'
when c.Myfield1='06' then '其他' when c.Myfield1='15' then '办公人员'
when c.Myfield1='17' then '铣床' when c.Myfield1='18' then 'TMA'
when c.Myfield1='19' then '其他/保洁员' when c.Myfield1='20' then 'N12'
when c.Myfield1='21' then 'X340' when c.Myfield1='22' then 'B9'
when c.Myfield1='23' then 'N56/N61' when c.Myfield1='24' then 'B170'
when c.Myfield1='09' then 'X241-Assy' when  c.Myfield1='11' then 'X241-Shaft'
when c.Myfield1='13' then 'X241-Ring' when c.Myfield1='14' then 'X241-Nut'
when c.Myfield1='12' then 'X241-Collar' when c.Myfield1='25' then 'B171' end),EmpTypeName,
count(*) as 人数,SUM(PayNo10) as 加班费,
SUM(PayNo20) as 应付,SUM(PayNo21+PayNo22+PayNo23+PayNo24+PayNo29) as 成本
from PayMonthData a left join AttMonthData b 
left join PerEmployee c on b.EmpID=c.EmpID
on a.EmpID=b.EmpID and a.YYMM=b.YYMM
where a.YYMM=@YYMM and c.Myfield1!='' and c.Myfield1 is not null
and EmpTypeName!='' and EmpTypeName is not null
and UserTypeName!='' and UserTypeName is not null
group by c.Myfield1,EmpTypeName,UserTypeName

declare @sql varchar(8000)

set @sql=''

select @sql=@sql+',['+ 成本归属+']' from TB_cost group by 成本归属
print(@sql)
set @sql=STUFF(@sql,1,1,'')
print(@sql)
set @sql='select * 
from TB_cost 
pivot(EmpTypeName,count(*),SUM(PayNo10),SUM(PayNo20),SUM(PayNo21+PayNo22+PayNo23+PayNo24+PayNo29) for 成本归属 in ('+@sql+')) t'

print(@sql)
exec(@sql)


---exec [dbo].[acmor_cost] '201506'

daile80 2015-07-24