Transposing rows to columns

So this is something I tend to run into fairly often in my line of work. Let's say I have a table of Customers and another table of Orders. One customer can have multiple orders. Here's an example:

Normally, if I want to query out all of the orders, along with customer info, I'd do something like this:

...and that would give me this:

Unfortunately, many of the people that consume this data would prefer to see the repeated information gone and have all orders on a single line. So they'd rather see a transpose of rows into columns, so that the resulting set is something like this:

Is there any decent way to do a transpose like this using SQL? I can write some nasty code that will do it, but it's always very painful to write and isn't easily reused for another query. I just thought I'd ask if anyone knew of a good way to transpose things like this using SQL.