v2008, I've used this transform a million times successfully for cross-tab/pivot resultsets:
it isn't working now, and i just am seriously tired of trying to fix it. my failure is this:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
I've read and read many references out there as to the cause of this, but it's not a CTE per se and again, i'm just seriously tired of working this one, i would like to use the new PIVOT in v2008.
every reference I've seen on PIVOT, however, suggests that I have to hard-code an IN list. surely I am misinterpretting this. for example:
SELECT VendorID,  AS Emp1,  AS Emp2,  AS Emp3,  AS Emp4,  AS Emp5
(SELECT PurchaseOrderID, EmployeeID, VendorID
( COUNT (PurchaseOrderID)
FOR EmployeeID IN ( , , , ,  ) <<<<<<<<<< THIS IN LIST
) AS pvt
ORDER BY pvt.VendorID;
i just want that code below to return this:
symbol1, symbol2, symbol3...........
date1 12 157 15
date2 100 6000 3599
date3 538 752 7
Do I have to do the PIVOT with dynamic sql?
CONVERT(VARCHAR(10),DateField,101) AS [Date],
SUM(quantity) AS Volume
AND (symbol LIKE 'xx%' OR symbol LIKE 'yy%' OR symbol LIKE 'zz%' OR symbol LIKE 'JJ%' or symbol IN ('aa','bb','cc')
OR Symbol LIKE 'klm__')
GROUP BY Symbol,CONVERT(VARCHAR(10),DateField,101)