PIVOT not working Incorrect syntax near ')'

Question

T-SQL code:

SELECT iCarrierInvoiceDetailsID, [1],[2],[3]
FROM [GroundEDI].[dbo].[tblCarrierInvoiceDetails]
PIVOT(MAX(dTotalCharge) FOR iCarrierInvoiceHeaderID IN ([1],[2],[3]))AS P

Error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.

Any idea why I am getting this error?

Answer 1

It looks like you are trying to directly select the pivot columns from the table itself and not the pivot. You will need to do something like this:

SELECT p.[1],p.[2],p.[3] 
FROM 
(SELECT iCarrierInvoiceHeaderID
       ,dTotalCharge
FROM [GroundEDI].[dbo].[tblCarrierInvoiceDetails]) t
PIVOT(MAX(dTotalCharge) FOR iCarrierInvoiceHeaderID IN ([1],[2],[3])
)AS P;