How to Unpivot Data with Dynamic Columns in MySQL When You Lack an UNPIVOT Function?

Unpivoting Data with Dynamic Columns

In the query transformation realm, unpivoting data plays a crucial role in reshaping table structures. One such scenario involves transposing dynamic columns into rows, a task that MySQL's lack of an UNPIVOT function can hinder.

Problem: Transposing Dynamic Columns

Imagine a table called Table_1 with dynamic columns (abc, brt, ccc, etc.) representing different attributes, as shown below:

Table_1
-----------------------------------------
Id       abc  brt ccc ddq eee fff gga hxx
-----------------------------------------
12345     0    1   0   5   0   2   0   0  
21321     0    0   0   0   0   0   0   0   
33333     2    0   0   0   0   0   0   0   
41414     0    0   0   0   5   0   0   1   
55001     0    0   0   0   0   0   0   2   
60000     0    0   0   0   0   0   0   0 
77777     9    0   3   0   0   0   0   0

The goal is to transform Table_1 into an Expected_Result_Table that lists the columns as rows, with only non-zero values:

Expected_Result_Table
---------------------
Id      Word   Qty>0
---------------------
12345    brt    1
12345    ddq    5
12345    fff    2
33333    abc    2
41414    eee    5
41414    hxx    1
55001    hxx    2
77777    abc    9
77777    ccc    3

Solution: Dynamic UNION ALL and Prepared Statement

To accomplish this transformation, we can leverage a combination of UNION ALL and a prepared statement to handle dynamic columns:

1. Generate Dynamic SQL: Using information_schema.columns, we can generate dynamic SQL that selects rows for each non-zero column.
2. Prepare Statement: We use a prepared statement to execute the dynamic SQL and avoid potential SQL injection vulnerabilities.
3. Execute Statement: We execute the prepared statement to retrieve the unpivoted data.

Here's the code for the dynamic solution:

-- Generate dynamic SQL
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;

-- Build the final SQL query
SET @sql 
  = CONCAT('select id, word, qty
           from
           (', @sql, ') x  order by id');

-- Prepare and execute the statement
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Result:

The resulting table will contain the desired rows:

Id      Word   Qty>0
---------------------
12345    brt    1
12345    ddq    5
12345    fff    2
33333    abc    2
41414    eee    5
41414    hxx    1
55001    hxx    2
77777    abc    9
77777    ccc    3

The above is the detailed content of How to Unpivot Data with Dynamic Columns in MySQL When You Lack an UNPIVOT Function?. For more information, please follow other related articles on the PHP Chinese website!