How to Dynamically Unpivot Columns into Rows in MySQL?

Transposing Dynamic Columns into Rows

Transposing columns into rows, often referred to as "unpivoting," can be a valuable technique in data reshaping. By converting wide tables with multiple columns into narrow tables with fewer columns, it becomes easier to analyze and work with the data.

Dynamic Unpivoting in MySQL

In MySQL, there is no built-in UNPIVOT function. However, you can achieve unpivoting using a combination of SQL statements and dynamic SQL generation.

Example: Unpivoting Table_1

Consider the following example, where we want to unpivot Table_1 into Expected_Result_Table, considering only values greater than 0:

Table1
-----------------------------------------
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

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

Dynamic SQL Generation for Unpivoting

To unpivot Table_1, we first need to generate a dynamic SQL statement based on the column names of Table_1. This can be achieved using the following steps:

1. Generate a list of distinct column names excluding the "Id" column using the following query:

   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;

2. Concatenate the generated SQL strings to form the final dynamic SQL statement:

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

3. Prepare and execute the dynamic SQL statement using the following code:

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

Result:

Executing the dynamic SQL statement will generate the 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

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