File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
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: 31808

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]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
jQuery in Action, 2nd edition
subject: Transposing rows to columns