Solved

Pivot not using aggregate yield error

Posted on 2016-11-30
3
94 Views
Last Modified: 2016-12-01
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
Comment
Question by:Lucia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 29

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41908321
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
 

Author Closing Comment

by:Lucia
ID: 41908671
Thanks, Lucia
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41909649
Welcome Lucia !!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question