Brock
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Welcome Lucia !!
ASKER