把你的语句改了一下,你试试:create table LocationInfo(
LocationInfoID int ,LocationName varchar(10)
)insert into LocationInfo
select 1000 ,'a1' union all
select 1001 ,'a2'create table PassengerLocationHourDetail
(LocationInfoID int,TypeID int,PeriodIN int,
DateHourKey int,DateKey int
)insert into PassengerLocationHourDetail
select 1000,1 ,100 ,2014010110 ,20140101 union all
select 1000,1 ,200 ,2014010111 ,20140101 union all
select 1001,1 ,300 ,2014010110 ,20140101 union all
select 1001,1 ,400 ,2014010111 ,20140101
go
--语句
declare @sql nvarchar(4000)set @sql = '' select @sql = @sql +',sum(case when b.datehourkey='+cast(b.DateHourKey as varchar)+' then b.PeriodIN else 0 end) as ['+cast(b.DateHourKey as varchar)+']'
from LocationInfo a
left join PassengerLocationHourDetail b
on a.LocationInfoID=b.LocationInfoID
where (left(b.DateHourKey,8) >=20140101 and left(b.DateHourKey,8)<=20140105)
and b.TypeID =1
group by b.DateHourKey
set @sql ='select a.LocationName' + @sql + ' from LocationInfo a
left join PassengerLocationhourDetail b
on a.LocationInfoID=b.LocationInfoID where left(b.datehourkey,8) >='+ cast(20140101 as varchar)+' and left(b.datehourkey,8)<='+ cast(20140105 as varchar)+' and b.typeid = 1 group by a.LocationName'
exec(@sql)
/*
LocationName 2014010110 2014010111
a1 100 200
a2 300 400
*/
LocationInfoID int ,LocationName varchar(10)
)insert into LocationInfo
select 1000 ,'a1' union all
select 1001 ,'a2'create table PassengerLocationHourDetail
(LocationInfoID int,TypeID int,PeriodIN int,
DateHourKey int,DateKey int
)insert into PassengerLocationHourDetail
select 1000,1 ,100 ,2014010110 ,20140101 union all
select 1000,1 ,200 ,2014010111 ,20140101 union all
select 1001,1 ,300 ,2014010110 ,20140101 union all
select 1001,1 ,400 ,2014010111 ,20140101
go
--语句
declare @sql nvarchar(4000)set @sql = '' select @sql = @sql +',sum(case when b.datehourkey='+cast(b.DateHourKey as varchar)+' then b.PeriodIN else 0 end) as ['+cast(b.DateHourKey as varchar)+']'
from LocationInfo a
left join PassengerLocationHourDetail b
on a.LocationInfoID=b.LocationInfoID
where (left(b.DateHourKey,8) >=20140101 and left(b.DateHourKey,8)<=20140105)
and b.TypeID =1
group by b.DateHourKey
set @sql ='select a.LocationName' + @sql + ' from LocationInfo a
left join PassengerLocationhourDetail b
on a.LocationInfoID=b.LocationInfoID where left(b.datehourkey,8) >='+ cast(20140101 as varchar)+' and left(b.datehourkey,8)<='+ cast(20140105 as varchar)+' and b.typeid = 1 group by a.LocationName'
exec(@sql)
/*
LocationName 2014010110 2014010111
a1 100 200
a2 300 400
*/
字符串 '201401' 后的引号不完整。
消息 102,级别 15,状态 1,第 1 行
'201401' 附近有语法错误。这是为什么呢?
你把exec(@sql) 改成 select @sql,然后把语句贴出来看看
,这只是截了一部分select a.LocationName,sum(case when b.datehourkey=2014010119 then b.PeriodIN else 0 end) as [2014010119],sum(case when b.datehourkey=2014010108 then b.PeriodIN else 0 end) as [2014010108],sum(case when b.datehourkey=2014010211 then b.PeriodIN else 0 end) as [2014010211],sum(case when b.datehourkey=2014010208 then b.PeriodIN else 0 end) as [2014010208],sum(case when b.datehourkey=2014010122 then b.PeriodIN else 0 end) as [2014010122],sum(case when b.datehourkey=2014010205 then b.PeriodIN else 0 end) as [2014010205],sum(case when b.datehourkey=2014010102 then b.PeriodIN else 0 end) as [2014010102],sum(case when b.datehourkey=2014010202 then b.PeriodIN else 0 end) as [2014010202],sum(case when b.datehourkey=2014010222 then b.PeriodIN else 0 end) as [2014010222],sum(case when b.datehourkey=2014010105 then b.PeriodIN else 0 end) as [2014010105],sum(case when b.datehourkey=2014010116 then b.PeriodIN else 0 end) as [2014010116],sum(case when