Toad World® Forums

Pivot / Crosstab in Toad 12.5.1 Question

I am struggling with trying to get a Pivot to work in Toad 12.5.1. I have tried this two ways. One based on an internet search that used Toad 11.0. The other based on MS Access SQL logic that worked using a table I created in Toad and linked to ODBC. Neither one will work in Toad. Please advise.

Internet Search

SELECT HMOCOMMALL_MEMS.membid AS MEMBID,
HMOCOMMALL_MEMS.YEARMON,
Sum(.HMOCOMMALL_MEMS.MMS) AS TotalMMS

FROM HMOCOMMALL_MEMS

PIVOT HMOCOMMALL_MEMS.YEARMON (‘201501’,‘201502’,‘201503’,‘201504’,‘201505’,‘201506’,‘201507’,‘201508’,‘201509’,‘201510’,‘201511’,‘201512’);

MS ACCESS

TRANSFORM Sum(HMOCOMMALL_MEMS.MMS) AS SumOfMMS

SELECT HMOCOMMALL_MEMS.MEMBID,
HMOCOMMALL_MEMS.YEARMON,

Sum(HMOCOMMALL_MEMS.MMS) AS TotalMMS

FROM HMOCOMMALL_MEMS

GROUP BY HMOCOMMALL_MEMS.MEMBERID
PIVOT (HMOCOMMALL_MEMS.YEARMON);