Unfortunately, MySQL does not have an UNPIVOT or a PIVOT function but they can be replicated using a UNION ALL for UNPIVOT and an aggregate function with CASE statement for the PIVOT. If you know the values that you want to transform, then you can hard-code then similar to the following:
select col code,
sum(case when code = 'xxxx' then value end) xxxx,
sum(case when code = 'xxxx' then value end) yyyy
from
(
select code, A value, 'A' col
from table1
union all
select code, B value, 'B' col
from table1
) x
group by col;
See SQL Fiddle with Demo
But you stated that you will have an unknown number of code values. If that is the case then you can use a prepared statement to pivot the data:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'SUM(case when code = ''',
code,
''' then value end) AS ',
code
)
) INTO @sql
FROM Table1;
SET @sql = CONCAT('SELECT col as code, ', @sql, '
FROM
(
select code, A value, ''A'' col
from table1
union all
select code, B value, ''B'' col
from table1
) x
GROUP BY col');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
See SQL Fiddle with Demo.
Both produce the same results:
| CODE | XXXX | YYYY |
----------------------
| A | 50 | 38 |
| B | 63 | 68 |
