Transforming data using SQL

Question

I have a table in my database formatted as follows:

customer old_code new_code C1 AX C1 BY C2 CY C2 DZ 

So, the client and old_code key pair are mapped to the new_code. This is great, since the way of storing data, search queries are very fast, but for human consumption, the data will be better displayed as follows:

  C1 C2 XA YBC ZD 

Is there an easy way to use SQL to convert data to a second view? Obviously, there can be any number of clients, although I could request a unique set before distribution. I currently have <50,000 entries, and I expect this to be the norm, but I would like for any solutions to scale to several hundred thousand, if possible. My application is currently targeting MySQL.

Answer 1

One standard way:

 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 will have more information about the uniqueness of the columns. I tested this in Oracle, where you usually did DECODE; I think CASE will work for you.

Answer 2

You can use some OLAP methods for this. If your tables are not huge, you can export your data to Excel and use pivot tables to modify your data in the layout you just mentioned.

There is also an open source Pentaho tool that can help. Runs in Java.

Answer 3

I think you might have to call PROCEDURE from within SELECT DISTINCT new_code... This procedure will use INSERT with subqueries of type C1=(SELECT old_code FROM firsttable WHERE customer='C1' AND new_code=code) , where code is the new_code passed as a parameter from SELECT DISTINCT .

I have never tried this before, so I'm not sure exactly if and how it will work, but this is where I will start conceptually.