我用查询语句查询出一张表，查询语句有where OilDate between '2013-08-01' and '2013-09-01'
OilGun OilState Specision OilDate Oil
1# 柴油 0# 2013-08-05 100
1# 柴油 0# 2013-08-28 200
2# 汽油 92# 2013-08-29 300
现在对此表进行行列转换，得到下表
OilGun OilState Specision 2013-08-05 2013-08-28 2013-08-29
1# 柴油 0# 100 200 0
2# 汽油 92# 0 0 300
我现在想把日期做成动态查询，做成存储过程，下面是我的语句
create proc oil1 as
declare @oil1 varchar(8000),@start_date date,@end_date date
begin
set @oil1='select OilGunNo,OilState,Specision'
select @oil1 = @oil1+', SUM(case OilDate when '''+OilDate+''' then Oil else 0 end) [' + OilDate + ']'from (select OilGunNo,OilState,Specision,CONVERT(VARCHAR(50),OilDate,110)as OilDate,sum(oil) as oil
from GB_oil where OilDate between CONVERT(VARCHAR(50),@start_date,110) and CONVERT(VARCHAR(50),@end_date,110)
group by OilGunNo,OilState,Specision,OilDate
)as a
set @oil1=@oil1+'from GB_oil group by OilGunNo,OilState,Specision'
exec(@oil1)
end
但运行存储过程时会提示消息 207，级别 16，状态 1，第 1 行
列名 'OilGunNo' 无效。
消息 207，级别 16，状态 1，第 1 行
列名 'OilState' 无效。
消息 207，级别 16，状态 1，第 1 行
列名 'Specision' 无效。
消息 207，级别 16，状态 1，第 1 行
列名 'OilGunNo' 无效。
消息 207，级别 16，状态 1，第 1 行
列名 'OilState' 无效。
消息 207，级别 16，状态 1，第 1 行
列名 'Specisionfrom' 无效。
请问怎么修改
------解决方案--------------------
------解决方案--------------------
大概这样，存储过程的参数你自己加
----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-08-28 13:32:11
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[GB_oil]
if object_id('[GB_oil]') is not null drop table [GB_oil]
go
create table [GB_oil]([OilGun] varchar(2),[OilState] varchar(4),[Specision] varchar(3),[OilDate] DATE,[Oil] int)
insert [GB_oil]
select '1#','柴油','0#','2013-08-05',100 union all
select '1#','柴油','0#','2013-08-28',200 union all
select '2#','汽油','92#','2013-08-29',300
--------------开始查询--------------------------
go
CREATE PROC test
AS
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([OilDate])+'=max(case when [OilDate]='+quotename([OilDate],'''')+' then [Oil] else 0 end)'
from [GB_oil] group by [OilDate]
exec('select [OilGun],[OilState],[Specision]'+@s+' from [GB_oil] group by [OilGun],[OilState],[Specision]')
go
EXEC test
----------------结果----------------------------
/*
OilGun OilState Specision 2013-08-05 2013-08-28 2013-08-29
------ -------- --------- ----------- ----------- -----------
1# 柴油 0# 100 200 0