Tunnel mode is commonly used between site-to-site VPN setups (although not exclusively) whereas transport mode is commonly used by remote individual workers accessing a corporate network from an endpoint terminal (e.g. a laptop).
The tunnel mode encapsulates the original IP packet and places a new outer IP header to route the packet between VPN endpoints. For example this new header could include the source and destination IP of the IPSec connected firewalls between two offices.
An advantage of tunnelling VPN is anyone sniffing the network knows nothing about the packet other than it's destined for the VPN endpoint and originated from the other VPN endpoint. The whole original IP packet is cryptographically protected to offer confidentiality. The packet when arriving at the destination has its outer IP header removed and the original packet is forwarded on.
Transport mode does not encapsulate another IP packet around the original IP header of the packet and the details of the routing is therefore viewable by sniffers. The TCP layer and payload part of the packet is though protected by confidentiality.
Tunnel mode can send many packets from different sources "across" sites which is why it is ideal for connecting remote offices together.
You can use transport mode and tunnel mode together. This may be advantageous if an office requires terminals to send confidential information from one terminal to another in another site, while offering protection to the data while it's travelling across the internet and the internal intranets at both ends.