转动商店程序

问题描述

DECLARE @t varchar(4000)
Declare @f varchar(4000)
declare @strs as varchar(4000)

CREATE TABLE #tempTab1(
--DateofObservation1 datetime,
SMW nvarchar(2),
VillageName nvarchar(200),
Result1 decimal(8,2)
 )

insert into #tempTab1(VillageName,SMW,Result1)

		select   [Village Name] ,SMW,AVG(Result) Result from
	(
	 Select DateofObservation1, SMW,VillageName as 'Village Name',Result from VillageDetails inner join (
		SELECT  DateofObservation DateofObservation1, dbo.GetStdWeek(DateofObservation) SMW, 
							  v.VillageID,cast(AVG(dbo.PestIncidenceData.ObservationValue)as numeric(10,2)) AS  'Result'
		FROM    dbo.PestIncidenceData INNER JOIN
				dbo.PestIncidenceMaster ON dbo.PestIncidenceData.PestIncidenceMasterID = dbo.PestIncidenceMaster.PestIncidenceMasterID INNER JOIN
				dbo.FieldDetails ON dbo.PestIncidenceMaster.FieldID = dbo.FieldDetails.FieldID
				inner join VillageDetails v on Fielddetails.VillageID=v.VillageID
				inner join DistrictDetails d on d.DistrictID=v.DistrictID
		WHERE  (d.DistrictID =34 ) and (dbo.FieldDetails.Season =1) AND (dbo.FieldDetails.FieldNumber LIKE 'Random%') 
		       AND (dbo.PestIncidenceData.PestID =1)  and ObservationValue<>99999
		       and (dbo.GetStdWeek(dateofObservation )between 45 and 45)
		GROUP BY dbo.PestIncidenceMaster.DateofObservation,v.VillageID) A on VillageDetails.VillageId=A.VillageId
			) as a  group by SMW,[Village Name]  order by SMW
		
		SET @t = ''
		SELECT @t = @t + ', '  + SMW From #tempTab1 group by SMW
		SELECT @f=STUFF(@t,1,2,'')	
		set @strs='SELECT VillageName,'+ @f + ' from  
		(select  VillageName,Result1 from #tempTab1) ps 
		PIVOT
		(
		Avg(Result1) 
		for SMW IN 
		('+ @f+')) As pvt'
		exec (@strs)
		drop table #tempTab1

注意：我在sql中执行此代码时遇到错误

note: i got error when execute this code in sql

(7 row(s) affected)
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '45'.

基本上我想转动商店程序查询的输出。

所以如果有人知道的话请帮助我。

basically i want to pivot the output of store procedure query.
so please help me if any one know.

推荐答案

这篇关于转动商店程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！