create procedure InsurancePivot
@city VARCHAR(30)
asBegin DECLARE @ColumnNames VARCHAR(3000) SET @ColumnNames='' SELECT @ColumnNames = @ColumnNames + '[' + stationname + '],' FROM ( SELECT DISTINCT stationname FROM rec_PM ) t SET @ColumnNames= LEFT(@ColumnNames, LEN(@ColumnNames)-1) DECLARE @selectSQL NVARCHAR(3000) SET @selectSQL= 'SELECT date,{0} FROM ( SELECT date,avg(pm25)pm25,stationname,stationcity FROM rec_PM where stationcity= '+ cast(@city as varchar(10)) +' group by stationid,date,stationname,stationcity
) p Pivot( Max(pm25) For stationname in ({0})) AS pvt order BY date' SET @selectSQL= REPLACE(@selectSQL,'{0}',@ColumnNames) exec sp_executesql @selectSQLend
在程序中把@city变量传到存储过程中 提示列名无效~我分析还是存储过程写的有问题,请各位高手赐教
@city VARCHAR(30)
asBegin DECLARE @ColumnNames VARCHAR(3000) SET @ColumnNames='' SELECT @ColumnNames = @ColumnNames + '[' + stationname + '],' FROM ( SELECT DISTINCT stationname FROM rec_PM ) t SET @ColumnNames= LEFT(@ColumnNames, LEN(@ColumnNames)-1) DECLARE @selectSQL NVARCHAR(3000) SET @selectSQL= 'SELECT date,{0} FROM ( SELECT date,avg(pm25)pm25,stationname,stationcity FROM rec_PM where stationcity= '+ cast(@city as varchar(10)) +' group by stationid,date,stationname,stationcity
) p Pivot( Max(pm25) For stationname in ({0})) AS pvt order BY date' SET @selectSQL= REPLACE(@selectSQL,'{0}',@ColumnNames) exec sp_executesql @selectSQLend
在程序中把@city变量传到存储过程中 提示列名无效~我分析还是存储过程写的有问题,请各位高手赐教
SELECT date,avg(pm25)pm25,stationname,stationcity FROM rec_PM where stationcity= '''+ cast(@city as varchar(10)) +''' group by stationid,date,stationname,stationcity
然后试试