CODEWITHSUNDEEP
sql-servert-sqlpivot
reilic
reilic

Reputation: 43

Pivot in T-SQL throws syntax error

I am trying to do a pivot in T-SQL that return SUM(INVOICE_AMOUNT) for 2015 and 2016 for each CUST_ID.

But the SQL I'm using throws an error:

Incorrect syntax near '('

and I can't identify the syntax error. Could someone advise what's wrong with it?

SELECT 
    H.CUST_ID, [2015], [2016]
FROM
    (SELECT  
         H.INVOICE_AMOUNT, YEAR(H.INVOICE_DT)
     FROM 
         BILLING_HEADER H) p
PIVOT
    SUM(H.INVOICE_AMOUNT) FOR YEAR(INVOICE_DT) IN ([2015], [2016])) AS pvt

The Year(INVOICE_AMOUNT) has static option of [2015] and [2016], so not sure what's wrong with the SQL..

Upvotes: 0

Views: 359

Answers (1)

qxg
qxg

Reputation: 7036

PIVOT operator expects column that contains the values that will become column headers after FOR keyword. Give YEAR(INVOICE_DT) an alias and use that alias after FOR

SELECT H.CUST_ID, [2015], [2016]
FROM
(
    SELECT H.INVOICE_AMOUNT, YEAR(H.INVOICE_DT) AS INVOICE_YEAR
    FROM BILLING_HEADER H
) p
PIVOT
    SUM(H.INVOICE_AMOUNT)
    FOR INVOICE_YEAR in ([2015],[2016])
) as pvt

Essentially, PIVOT operator perform a GROUP BY operation, and it guesses which column should be put into GROUP BY clause by removing columns being used in PIVOT operator from source table column. Such guess doesn't work with function. See a little information at TSQL - Looking for code clarification

Upvotes: 2

Related Questions