Can MySQL convert columns into rows, dynamically adding as many columns as are needed for the rows. I think my question might be related to pivot tables but I'm unsure and I don't know how to frame this question other than by giving the following example.
Given a two tables A and B, which look like
Table A
+--+-----+----+
|id|order|data|
+--+-----+----+
|1 |1 |P |
+--+-----+----+
|2 |2 |Q |
+--+-----+----+
|2 |1 |R |
+--+-----+----+
|1 |2 |S |
+--+-----+----+
I like to write a query that looks like the following:
Result Table
+--+-----+-----+
|id|data1|data2|
+--+-----+-----+
|1 |P |S |
+--+-----+-----+
|2 |R |Q |
+--+-----+-----+
Basically I want to turn each row in table B into a column in the result table. If there was a new entry was added to table B for id=1, then I want the result table to automatically extend by one column to accommodate this extra data point.
You can use GROUP BY
and MAX
to simulate pivot. MySQL also supports IF
statement.
SELECT ID,
MAX(IF(`order` = 1, data, NULL)) data1,
MAX(IF(`order` = 2, data, NULL)) data2
FROM TableA
GROUP BY ID
If you have multiple values of order
, dynamic SQL may be more appropriate so that you will not have to modify the query:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(`order` = ', `order`, ',data,NULL)) AS data', `order`)
) INTO @sql
FROM TableName;
SET @sql = CONCAT('SELECT ID, ', @sql, '
FROM TableName
GROUP BY ID');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
OUTPUT OF BOTH QUERIES:
╔════╦═══════╦═══════╗
║ ID ║ DATA1 ║ DATA2 ║
╠════╬═══════╬═══════╣
║ 1 ║ P ║ S ║
║ 2 ║ R ║ Q ║
╚════╩═══════╩═══════╝