There is an extra not needed ( before FROM. Remove it and the query should work.
I'm not sure that's the only issue - the final SELECT in conjunction with the PIVOT seemed like it needed more. But without the data, wasn't positive. That's why I suggested the sanity check of the static base query first.