aspose file tools*
The moose likes JDBC and the fly likes Transposing rows to columns Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Transposing rows to columns" Watch "Transposing rows to columns" New topic
Author

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
Marshal

Joined: May 26, 2003
Posts: 30752
    
156

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


[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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Transposing rows to columns