Data transformation with SQL

Question

I have a table in my database formatted like this:

customer   old_code   new_code
C1         A          X
C1         B          Y        
C2         C          Y
C2         D          Z

So the pair of keys customer and old_code map to a new_code. This is great as a way of storing the data, looks ups are very fast, but for human consumption the data would be better displayed like this:

      C1    C2
X     A
Y     B     C
Z           D

Is there an easy way using SQL to transform the data to the second view? Obviously there can be any number of customers although I could query for the unique set before hand. Currently I have < 50000 records and I expect that to be the norm but I'd like any solutions to scale up to a few hundred thousand if possible. My application currently targets MySQL.

Answer 1

You could use some OLAP techniques to do that. If your tables aren't huge, you can export your data to Excel and use pivot tables to rearrange your data in the layout you've just mentioned.

There is also an open source tool called Pentaho that might help. Runs on Java.

Answer 2

I think you may have to call a PROCEDURE from within a SELECT DISTINCT new_code.... The procedure would use INSERT statements with subqueries like C1=(SELECT old_code FROM firsttable WHERE customer='C1' AND new_code=code) where code is the new_code passed in as a param from the SELECT DISTINCT.

I've never tried this before so I'm not sure exactly if or how it would work, but this is where I would start conceptually.

Answer 3

One standard way would be:

SELECT CC.NEW_CODE,
       MAX(CASE CUSTOMER WHEN 'C1' THEN OLD_CODE ELSE NULL END) C1,
       MAX(CASE CUSTOMER WHEN 'C2' THEN OLD_CODE ELSE NULL END) C2
FROM CUSTOMER_CODE CC
GROUP BY CC.NEW_CODE
ORDER BY CC.NEW_CODE

Of course, this depends on some assumptions and you would have more information on the uniqueness of the columns. I tested this in Oracle, where you'd usually do DECODE; I think CASE should work for you.