I have a pivot query, which is;
SELECT AttendanceDate, [C100] AS C1, [C200] AS C2
FROM (SELECT AttendanceDate, Derived_Provider
WHERE (AttendanceDate BETWEEN CONVERT(DATETIME, '03 /01/ 2007 00:00:00', 103) AND CONVERT(DATETIME, '05/03/2007 00:00:00', 103)))
p PIVOT (COUNT(Deriverd_Provider) FOR Derived_Provider IN (C100, C200)) AS pvt
ORDER BY AttendanceDate
I would like to change the IN clause to pick up values which are LIKE C100. This also means I will have to change the first select clause to pick up variations of C100. I am not sure how to do either of these tasks.