My 2004 Pilot EXL is still great after 8 years and over 200,000 miles.
I bought it in spring of '06 when it had 50k miles, so someone before me drove a lot, too. I've not had all the issues I've read about, but I don't abuse the vehicle. I get oil changes every 5-6k miles, but do spread out the scheduled maintenances and fluid changes a bit more due to quickly rolling up highway miles.
My Pilot replaced a Jeep Grand Cherokee Limited 4x4 that had the gas-guzzling 5.2 liter V8. The main reason I replaced the JGC was due to terrible gas mileage (9-15) and slipping transmission. I've not had any transmission issues with my Pilot, but I don't abuse it, either!
Very comfortable seats, good gas mileage for an AWD SUV, good cargo space and 3rd row seating when needed, smooth ride on freeways and maneuverability in city traffic, fewer blind spots than in other SUVs, and nice styling (not over the top like where some SUVs have gone). While this is mainly a suburban/city vehicle, I do take it regularly to our cabin in the mountains which has a hilly gravel road and it does fine with the steep drive, slippery gravel and occasional mud--but I don't treat it like I'm going 4-wheeling in my nephew's old-time Jeep CJ!
The only thing I don't *love* about my '04 Pilot is that the rear tailgate window doesn't open independently of the tailgate, but this window does open in newer models. Other than that, I hope Honda has corrected whatever transmission issues some other owners have had.