Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 115
  • Last Modified:

Pivot not using aggregate yield error

Hello,

What is wrong with this?


BEGIN TRANSACTION
UPDATE tn
  SET tn.[NC_HIST_POST_1] = SubQuery.[2015]
  FROM PS_NC_BUDG_STG_UPL tn  JOIN (
 SELECT *
  FROM (
 SELECT ACCOUNT
 , DEPTID
 , PROGRAM_CODE
 , PROJECT_ID
 , POSTED_TOTAL AS POSTED_TOTAL
 , BUDGET_PERIOD
  FROM PS_NC_BUDG_STG_HIS AS p PIVOT ((p.[POSTED_TOTAL]) FOR [BUDGET_PERIOD]  ([2014]
  , [2015]
  , [2016])) AS pvt
  GROUP BY ACCOUNT
  ,DEPTID
  , PROGRAM_CODE
  , PROJECT_ID
  , [2014]
  , [2015]
  , [2016] ) AS SubQuery ON SubQuery.ACCOUNT = tn.ACCOUNT
   AND SubQuery.PROGRAM_CODE = tn.PROGRAM_CODE
   AND SubQuery.PROJECT_ID = tn.PROJECT_ID
   AND SubQuery.DEPTID = tn.DEPTID
   AND ([2015] IS NOT NULL)


Thanks, Lucia
0
Lucia
Asked:
Lucia
  • 2
1 Solution
 
Pawan KumarDatabase ExpertCommented:
Please try...

BEGIN TRANSACTION 
UPDATE tn 
  SET tn.[NC_HIST_POST_1] = SubQuery.[2015] 
  FROM PS_NC_BUDG_STG_UPL tn  
  JOIN 
  ( 
	  SELECT * 
	  FROM 
	  ( 
			 SELECT 
				ACCOUNT 
				, DEPTID 
				, PROGRAM_CODE 
				, PROJECT_ID 
				, POSTED_TOTAL AS POSTED_TOTAL 
				, BUDGET_PERIOD 
				FROM PS_NC_BUDG_STG_HIS AS p PIVOT ((p.[POSTED_TOTAL]) FOR [BUDGET_PERIOD]  ([2014] , [2015] , [2016])) AS pvt 
				GROUP BY ACCOUNT 
				,DEPTID 
				, PROGRAM_CODE 
				, PROJECT_ID 
				, [2014] 
				, [2015] 
				, [2016] 
		)u
   )AS SubQuery 
       ON SubQuery.ACCOUNT = tn.ACCOUNT 
   AND SubQuery.PROGRAM_CODE = tn.PROGRAM_CODE 
   AND SubQuery.PROJECT_ID = tn.PROJECT_ID 
   AND SubQuery.DEPTID = tn.DEPTID 
   WHERE [2015] IS NOT NULL

Open in new window


Hope it helps !!
0
 
LuciaAuthor Commented:
Thanks, Lucia
0
 
Pawan KumarDatabase ExpertCommented:
Welcome Lucia !!
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now