How to use a CTE to create a crosstab query

You can use a CTE with the ROW_NUMBER function to create a crosstab query. For example, say your data structure stores various types of event in a single table, but you need to report the various event dates in separate columns for each type of event. Here is how you might do this using a CTE joining on the output produced by ROW_NUMBER:

WITH eventTypeX AS
(
SELECT e.EventDate, e.JobID,
ROW_NUMBER() OVER(PARTITION BY e.JobID ORDER BY e.EventDate ASC) AS rn
FROM dbo.tblEvents
WHERE...
),
eventTypeY AS
(
SELECT e.EventDate, e.JobID,
ROW_NUMBER() OVER(PARTITION BY e.JobID ORDER BY e.EventDate ASC) AS rn
FROM dbo.tblEvents
WHERE...
)
SELECT j.JobID, j.JobDescription, e1x.EventDate AS FirstEventTypeX,
e2x.EventDate AS SecondEventType1, e1y.EventDate AS FirstEventTypeY,
e2y.EventDate AS SecondEventTypeY
FROM tblJobs j
LEFT OUTER JOIN eventTypeX e1x ON j.JobID = e1x.JobID AND e1x.rn = 1
LEFT OUTER JOIN eventTypeX e2x ON j.JobID = e2x.JobID AND e2x.rn = 2
LEFT OUTER JOIN eventTypeY e1y ON j.JobID = e1y.JobID AND e1y.rn = 1
LEFT OUTER JOIN eventTypeY e2y ON j.JobID = e2y.JobID AND e2y.rn = 2