2

I got a set of tables which have been imported into a database by transforming them like shown in the following picture:

Table Transformation

Now it is necessary to get the former tables back. Unfortunately the guy who did the importing finished school and is thus is unavailable. Therefore I was asked to find an SQL-based solution to back-transform the tables. I would be able to write a small tool to solve this problem, but I've got no idea how to solve this with SQL.

With Kind Regards,

mikkael
  • 21
  • 2

2 Answers2

1

Something like this might do the trick (here's the SQLFiddle)

SELECT 
Line,
MAX(CASE WHEN `Column` = 'C1' THEN `Value` END) AS C1,
MAX(CASE WHEN `Column` = 'C2' THEN `Value` END) AS C2,
MAX(CASE WHEN `Column` = 'C3' THEN `Value` END) AS C3,
MAX(CASE WHEN `Column` = 'C4' THEN `Value` END) AS C4
FROM YourTable
GROUP BY Line

Like Sascha suggested in their answer, you might want to consider either creating this as a view, or creating a view that looks like Table 2 once you've finished putting the table back to how it was. That should prevent this kind of problem popping up again in the future.

Nathan Jolly
  • 3,720
  • 18
  • 26
0

For SQLserver you could simply use SELECT...PIVOT

For MySQL the problem has been addressed here: https://stackoverflow.com/questions/1241178/mysql-rows-to-columns

A better solution than pivoting the data into a table, would be the usage of a pivot VIEW and leaving the tables unpivoted, btw.

Community
  • 1
Sascha Rambeaud
  • 525
  • 2
  • 5