CODEWITHSUNDEEP
sqlmysql
wobblycogs
wobblycogs

Reputation: 4093

Data transformation with SQL

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.

Upvotes: 6

Views: 784

Answers (3)

orbfish
orbfish

Reputation: 7741

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.

Upvotes: 1

DLH
DLH

Reputation: 2821

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.

Upvotes: 0

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181350

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.

Upvotes: 0

Related Questions