?
Solved

HOW TO TRANSPOSE COLUMN DATA IN DB2 UDB

Posted on 2007-08-01
5
Medium Priority
?
2,394 Views
Last Modified: 2008-09-14
I have a table like this

ID           NAME         NO
ARC       BOB           AC4
ARC       JUNE           GT4
ARC       JOHN           HJ7
GYU        SAR            JI2
GYU         SUE            HU2


I  WANT TO GET ALL NAMES AS COLUMNS AND NO'S  AS THEIR DATA  FOR ' ARC'
AND I DONT KNOW THE COUNT OF NAMES.

RESULT SHOULD BE LIKE THIS
BOB   JUNE  JOHN  ------- COLUMNS
AC4    GT4   HJ7                DATA
0
Comment
Question by:icemine
  • 2
3 Comments
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 19613843
Hi icemine,

There are several ways to do this, but none are particularly easy.

The simplest is to use the extensions in UDB 9.x/DWE.  But they are relatively expensive.  There's a good chance that you don't have them.

If you know the maximum number of names that you'll place across, you can repeatedly join the table to itself.  Since you've already said that you don't know how many names are there, this may not be practical.  An if you have a large amount of data, this can be an long running query.

Another option is to use a client that supports pivot tables, load the data from DB2, and let the client do the work.

But within DB2 there is a way.  Recursive SQL.  It can take a little bit to get your head wrapped around, but it's efficient and will do what you want.  Take a look at the link below.  It shows a recursive SQL solution to a problem similar to yours.  The biggest difference is that your solution will require building two strings -- the list of names and the list of data items.  If this sounds like a solution that will work for you I'll be glad to help you write the SQL.


Kent


0
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 2000 total points
ID: 19613845


Link:  http://www.experts-exchange.com/Database/DB2/Q_22642179.html


Well, it IS below where I first mentioned it.  :)
0
 

Author Comment

by:icemine
ID: 19621870
Thank you so much for you reply. I will go through the link.

0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses
Course of the Month13 days, 18 hours left to enroll

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question