It's not a secret anymore!
The moose likes JDBC and Relational Databases and the fly likes Transposing rows to columns Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Transposing rows to columns" Watch "Transposing rows to columns" New topic

Transposing rows to columns

Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
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.

SCJP Tipline, etc.
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33130

The decode function can help. It's still a bunch of code though. See an example.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
I agree. Here's the link:
subject: Transposing rows to columns
jQuery in Action, 3rd edition