Here is a short list of single player games NOT made by id Software that I think are great.

Demon's Souls

S.T.A.L.K.E.R.: Shadow of Chernobyl (slow loading, minor gameplay issues and a bit buggy)

Portal

Oblivion

Fallout 3 (very buggy on PS3)

While not all games will appeal to everyone, some are buggy, and I would not recommend them to everyone either, I enjoyed these the games the most over the last 10 years.

I'm curious as to what non id Software games others have really enjoyed.

I like HalfLife 2 - still didn't complete it though.. I don't play it very often. COD2 was ok too.

The Half-Life series is really good, and so is portal. Infact Valve in general is one of the few developers I would blindly buy a game from.

I agree with cityy and Cheetah. The Half-Life and Half-Life 2 series; Half-Life, Half-Life: Opposing Force, Half-Life: Blue Shift, Half-Life 2, Half-Life 2: Episode 1 and Half-Life 2: Episode 2 by Valve Software is really good and fun to play. I loved to play both series to the point where I would finish game; Once you play it the first time, you don't want to stop. This was probably because of the amazing storyline, ambient sounds, location and music of Half-Life and the amazing details, atmosphere, ambient sounds, music and storyline of Half-Life 2.

Portal is another game by Valve Software and is really fun to play. The puzzles make you think on "How do I progress to the next level..." and require an amount of thinking to solve (Depending on your IQ number).

Age of Empires and the Age of Empires 2 Series; Age of Empires 2: The Age of Kings and Age of Empires 2: The Conquerors Expansion by Ensemble Studios are extraordinary Real Time Strategy (RTS) games. I love playing Age of Empires and the Age of Empires 2 Series.

I also really enjoyed Dark Reign 2 by Pandemic Studios because of the high quality graphics, storyline and the idea behind Dark Reign 2.

Red Alert 2 by Westwood Pacific and Red Alert 3 by EA Los Angeles because of the storyline, graphics and ambient sounds.

The Eight Single Player games (Just to name a few games) that I really enjoyed and think that they are great:

Half-Life Series
Half-Life 2 Series
Portal
Age of Empires
Age of Empires 2 Series
Dark Reign 2
Red Alert 2
Red Alert 3

These Single Player games are just my preference of great games and they will not appeal to everybody's liking and preference.

PaN61

Half-Life 2 would be on the top of my "not made by id" games.

Undying would be #2 on my list.

I also love Age of Empires 1 & 2 including their expansion packs.
Another one I've enjoyed quite a lot was Midtown Madness 2.
I also liked Elite Force (the first one) , but that's because I'm a Star Trek guy.

Half-Life: Opposing Force and Half-Life: Blue Shift were two games that I've enjoyed, I loved their concept, the fact that they were somewhat paralell stories to the main game.

Valve for left 4 dead and left 4 dead 2 excellent games and support for mods.
Dice for bad company 2 and previous battlefield games.
Thats the latest games i play more often.

@Incognito: Sure, but they are multi-player games, the focus here is on single player games :]

They also have a single player campaign ....)

Haha, yer OK.

To me, the single player campaign of Left 4 Dead and and the Battlefield games are more of a level tutorial, a get-to-know-the-level thing, but sure I guess for some they could be seen a single player game :]

I havent played many other non-id single player FPS games. Maybe some of Terminator, but I never got to finishing it as it never really appealed to me that much. Then I played against bots on the single player campaign of Unreal Tournament on PS2, though Im not sure if that counts.

I have, though, played a lot of non-FPS single player games, such as Age of Empires, as mentioned above. I even designed some levels for that game too, none of them ever got finished though. Maybe I should get back to work on those...
Edited 44 seconds after the original posting.

Half-Life/Opposing Force/Blue Shift <-- some of the best single-player action ever!
Painkiller <-- although I wasn't a big fan of it overall, it was still fun at times.
Postal 2 <-- a game full of LOLs and WTFs
Grand Theft Auto III/Vice City/San Andreas <-- while not FPS, still very entertaining. I'm a big fan of open-world gaming.

That's all I can think of right now.
Edited 1.37 minutes after the original posting.

I know this is somewhat controversial to some, but personally Ive enjoyed Halo CE, SW Battlefront,SW Republic Commando, SW Empire At war and dawn of war. Those are some of my favorite games that I can think of right off the spot. All have single player.

Fallout, Oblivion, Morrowind- everything bethesda is just excellent!
Gnome