• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Transposing rows to columns

 
Corey McGlone
Ranch Hand
Posts: 3271
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

Thanks, folks.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34851
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The decode function can help. It's still a bunch of code though. See an example.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic