MySQL 将动态列转置为行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16359345/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Transposing Dynamic Columns to Rows
提问by Romualdo Alves
I'd like to know how to unpivot Table_1into Expected_Result_Table:
我想知道如何Table_1转入Expected_Result_Table：
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
So, How to transpose columns in Table_1resulting in Expected_Result_Table, considering only values > 0?
那么，如何将列转置Table_1为Expected_Result_Table，仅考虑值 > 0？
回答by Taryn
MySQL does not have an UNPIVOT function, but you can convert your columns into rows using a UNION ALL.
MySQL 没有 UNPIVOT 函数，但您可以使用UNION ALL.
The basic syntax is:
基本语法是：
select id, word, qty
from
(
select id, 'abc' word, abc qty
from yt
where abc > 0
union all
select id, 'brt', brt
from yt
where brt > 0
) d
order by id;
In your case, you state that you need a solution for dynamic columns. If that is the case, then you will need to use a prepared statement to generate dynamic SQL:
在您的情况下，您声明您需要动态列的解决方案。如果是这种情况，那么您将需要使用准备好的语句来生成动态 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;
SET @sql
= CONCAT('select id, word, qty
from
(', @sql, ') x order by id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
回答by RichardTheKiwi
You are basically unpivoting the data from columns into rows, for which you can use UNION ALL. The filters can be applied across the unpivoted subquery, or individually to the parts.
您基本上是将列中的数据逆透视为行，为此您可以使用 UNION ALL。过滤器可以应用于非透视子查询，也可以单独应用于各个部分。
select id, Word, Qty from
(
select id, 'abc' Word, abc Qty from table1
union all
select id, 'brt', brt from table1
union all
select id, 'ccc', ccc from table1
union all
select id, 'ddq', ddq from table1
union all
select id, 'eee', eee from table1
union all
select id, 'fff', fff from table1
union all
select id, 'gga', gga from table1
union all
select id, 'hxx', hxx from table1
) x
where Qty > 0
order by id;
