转动商店程序 [英] pivot the store procedure

问题描述

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.

推荐答案

请阅读我的命令。



在我看来，嵌套的 SELECT 语句太多了。这可能是麻烦的原因。我强烈建议重新编写此查询以使其更易读，最后提高性能。

Please, read my command to the question.

In my opinion, there's too many nested SELECT statements. It might be the reason of trouble. I do strongly recommend to re-write this query to make it more readible and finally to improve performance.

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