Hi Michelle,
Here is a small sample I put together with your outline:
--Create Leaf table
create table leaf (ROUTEID INT, CYCLE VARCHAR(10), LAST_EDIT DATE);
--Insert records
INSERT INTO LEAF VALUES (1, 'CYCLE1', '10/23/2015');
INSERT INTO LEAF VALUES (1, 'CYCLE2', '1/23/2015');
INSERT INTO LEAF VALUES (1, 'CYCLE3', '3/23/2015');
INSERT INTO LEAF VALUES (2, 'CYCLE1', '5/23/2015');
INSERT INTO LEAF VALUES (2, 'CYCLE2', '12/23/2015');
INSERT INTO LEAF VALUES (2, 'CYCLE3', '7/23/2015');
--Create the select statement with a pivot
SELECT ROUTEID, CYCLE1, CYCLE2, CYCLE3 FROM
(SELECT ROUTEID, CYCLE, LAST_EDIT FROM LEAF) LEAF_PIV
PIVOT (MAX(LAST_EDIT) FOR CYCLE IN (CYCLE1, CYCLE2, CYCLE3)) PIV
Results:
In addition, this query does work for me in ArcMap/ArcCatalog if I cast the dates to varchar. What field types do you have for your last_edit field?