The problem: I had a query in Oracle that runs smoothly:

SELECT * FROM 
(
 SELECT  DT_ALIQUOT, CD_TYPE, AM_VALUE 
 FROM    EXPRESSION e , ALIQUOT al
 WHERE   e.ID_ALIQUOT = al.ID_ALIQUOT AND
         al.ID_SUBJECT = 50 AND
         al.ID_EXPERIMENT = 'E13' AND
         e.CD_TYPE IN ('rbc','platelets') 
)
PIVOT 
(
  SUM(AM_VALUE)
  for CD_TYPE IN ('rbc','platelets')
)
ORDER BY DT_ALIQUOT;

and I needed to replicate it in SQL Server. I kept receiving the error  “Incorrect syntax near the keyword ‘PIVOT’.”  in SQL Server. I was baffled for I thought that this type of query was standard.

The solution: SQL Server is very peculiar in the way it processes pivots, and that seems to be the cause for so many people asking for ways to solve the “Incorrect syntax near the keyword ‘PIVOT’.”   The query in SQL Server that actually works in SQL Server is:

SELECT * FROM 
(
 SELECT DT_ALIQUOT, CD_TYPE, AM_VALUE 
 FROM EXPRESSION e , ALIQUOT al
 WHERE e.ID_ALIQUOT = al.ID_ALIQUOT AND
  al.ID_SUBJECT = 50 AND
  al.ID_EXPERIMENT = 'E13' AND
  e.CD_TYPE IN ('rbc','platelets') 
) as X
PIVOT 
(
 SUM(AM_VALUE)
 for CD_TYPE IN ([rbc],[platelets])
) as Y
ORDER BY DT_ALIQUOT;

Note important differences: SQL Server requires aliases before and after PIVOT; you can pick any valid alias, and I used X and Y only as examples. Second, inside the PIVOT statement, you cannot use single quotes for ‘rbc’ and ‘platelets’ in this example, even though they are data.

That’s it. Happy SQL’ing!