How to Transpose Dynamic Columns to Rows in MySQL with Values Greater Than 0?

Transposing Dynamic Columns to Rows: Achieving the Desired Transformation

Introduction:
Unpivoting data from columns into rows is a common operation in data manipulation. This article explores how to transpose dynamic columns in Table_1, considering only values greater than 0, to obtain the desired Expected_Result_Table.

MySQL's UNPIVOT Function:
MySQL does not natively provide an UNPIVOT function. However, the following methods can be employed to achieve the desired result:

Static Column-Based Approach:
For a limited number of static columns, you can use a series of UNION ALL statements to create the transposed table. Each statement would select specific columns as Word and Qty. This approach is suitable for static schemas with a known number of columns.

Dynamic Column-Based Approach:
For dynamic schemas with an unknown number of columns, a more sophisticated approach is required. You can use a prepared statement to generate dynamic SQL that will pivot the columns. This involves dynamically creating the UNION ALL statements for each column.

Example for Dynamic Columns:
The provided MySQL query demonstrates how to transpose the columns in Table_1 using a dynamic 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;

Conclusion:
By using either the static or dynamic approach, you can effectively transpose dynamic columns in MySQL, considering only values greater than 0, to obtain the desired row-based structure represented by the Expected_Result_Table.

The above is the detailed content of How to Transpose Dynamic Columns to Rows in MySQL with Values Greater Than 0?. For more information, please follow other related articles on the PHP Chinese website!