How to Unpivot Dynamic Columns into Rows in MySQL?

Creating Rows from Dynamic Columns

In the realm of data management, manipulating columns and rows is often necessary. This question delves into a specific task: converting multiple dynamic columns into a new table with rows containing specific criteria.

The given example illustrates the unpivoting of Table_1, resulting in Expected_Result_Table. The key difference is that Expected_Result_Table displays each non-zero value in a separate row.

MySQL lacks a direct UNPIVOT function, but this limitation can be overcome using a UNION ALL statement. The basic approach involves creating separate SELECT statements for each column, selecting the ID, the column name as the "word" value, and the column value as the "qty" value. These SELECT statements are then combined using UNION ALL.

For example:

SELECT id, 'abc' AS word, abc AS qty
FROM yt
WHERE abc > 0
UNION ALL
SELECT id, 'brt', brt
FROM yt
WHERE brt > 0

This approach becomes more complex when dealing with dynamic columns. To address this, prepared statements can be employed. They allow for the dynamic generation of SQL statements based on metadata.

The following code snippet demonstrates such an approach:

SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'select id, ''',
      c.column_name,
      ''' as word, ',
      c.column_name,
      ' as qty 
      from yt 
      where ',
      c.column_name,
      ' > 0'
    ) SEPARATOR ' UNION ALL '
  ) INTO @sql
FROM information_schema.columns c
WHERE c.table_name = 'yt'
  AND c.column_name NOT IN ('id')
ORDER BY c.ordinal_position;

SET @sql 
  = CONCAT('select id, word, qty
           from
           (', @sql, ') x  order by id');


PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

In this example, the dynamic SQL is constructed using information from the information_schema.columns table. The prepared statement stmt is then used to execute the dynamically generated SQL, effectively unpivoting the dynamic columns into rows.

By implementing this approach, it is possible to transform dynamic columns into rows, catering to specific data needs.

The above is the detailed content of How to Unpivot Dynamic Columns into Rows in MySQL?. For more information, please follow other related articles on the PHP Chinese website!