Here's my take on this question, since I find the other two answers lacking actual examples.
Knight promotion
In the situation below black has been trying to prevent white from promoting the pawn (white has earlier given up his rook for a black pawn). White manages to promote, but only one of the promotions secures a (tablebase) draw.
[white "Alexander Evdokimov"]
[black "Evgeni Ellinovich Sveshnikov"]
[event "56th Russian Championships, 2003"]
[fen "8/r3KP2/8/4k3/8/8/8/8 w - - 0 1"]
1.Ke8 Ke6 2. f8=N+! {2. ... Ra8+ otherwise} 1/2-1/2
Here, white underpromotes to avoid stalemate.
[white "Iroda Khamrakulova"]
[black "Ekaterina Ubiennykh"]
[event "WJun Women, 2001"]
[fen "6r1/k1P5/P7/1K1R4/8/8/8/8 w - - 0 1"]
1. Rd8 {according to tablebases, Rd7 wins faster} Rxd8 {again, there are slower ways for black to lose} 2. cxd8=N! {stalemate if rook or queen is promoted, a draw if a bishop (wrong corner)} 1-0
In the situation below, white has just played Qg3. Black underpromotes to gain a tempo, as white threatens checkmate and fork rival king and queen.
[white "Zvonimir Mestrovic"]
[black "Svetozar Gligoric"]
[event "Hastings, 1971/72"]
[fen "7k/1p2Npbp/8/2P5/1P1r4/3b2QP/3q1pPK/2RB4 b - - 0 1"]
1. f1=N+! 0-1
In the position below, white underpromotes to deliver a checkmate.
[white "William Strum"]
[black "John McManus"]
[event "Eastern Open, 1997"]
[fen "8/p3R2P/1p4k1/6p1/2rB2KR/P1r5/8/8 w - - 0 1"]
1. h8=N+ 1-0
Last, but not least, a very neat opening trap. Black undepromotes to gain a tempo and subsequently win the white queen.
[fen "rnbqkbnr/pppppppp/8/8/8/8/PPPPPPPP/RNBQKBNR w KQkq - 0 1"]
1. d4 d5 2. c4 e5 3. dxe5 d4 4. e3 Bb4+ 5. Bd2 dxe3 6. Bxb4 exf2+ 7. Ke2 fxg1=N+ 8. Rxg1 Bg4+ 0-1
Rook promotion
Arguably, less spectacular than knight underpromotions. One would promote to a rook in order to avoid a stalemate (otherwise, anything a rook can do, a queen also can). Two examples from endgames below.
[fen "6k1/6P1/5K2/8/8/6P1/8/8 w - - 0 1"]
1. g4 Kh7 2. Kf7 Kh6 3. g8=R! {stalemate in case of queen promotion. Knight promotion would also win, but would take longer} 1-0
.
[fen "4r3/5RPk/5K2/8/8/8/8/8 w - - 0 1"]
1. Rf8 Rxf8 2. gxf8=R {stalemate in case of queen promotion, unable to win if promoting to a minor piece} 1-0
Bishop promotion
These are the rarest of the bunch. Usually would be to avoid a stalemate, or some other extraordinary situations, since otherwise, anything a bishop can do, a queen also can. First, a classic.
[fen "4Q3/Pq4pk/5p1p/5P1K/6PP/8/8/8 w - - 0 1"]
1. a8=B! {promoting to rook or queen runs into Qf7+ and stalemate} 1-0
Another endgame.
[fen "8/8/5b2/8/K7/4N3/kp6/8 w - - 0 1"]
1. Nd1 b1=B! {Nc3+ if promoting to rook or queen - draw} 0-1
Lastly, this amazing position.
[fen "1r5K/6PP/8/8/8/1k4q1/6P1/8 w - - 0 1"]
1. g8=B+! {black must move the king and it is a stalemate} Kb4 (1... Rxg8+ 2. hxg8=Q+ Qxg8+ 3. Kxg8 {and the g2 pawn will inevitably promote - white wins}) 1/2-1/2
Sources for these and many more examples
http://timkr.home.xs4all.nl/chess2/minor.htm
http://www.chessgames.com/perl/chesscollection?cid=1000028
