A worker has died after a burst water pipe caused a trench he was working in to collapse on top of him, emergency services have confirmed.