移调动态列到行
我想知道如何逆转置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
那么,如何转列Table_1
导致Expected_Result_Table
,只考虑值> 0 ?
MySQL没有一个UNPIVOT功能,但是您可以将列转换为使用UNION ALL
行。
的基本语法是:
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;
在你的情况,你的状态,你需要动态列的解决方案。如果是这样的话,那么你将需要使用准备好的语句来生成动态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;
基本上,您可以将列中的数据转化为行,您可以使用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;
[SQL小提琴演示](http://sqlfiddle.com/#!2/ b0934/6/0) – RichardTheKiwi 2013-05-03 13:09:52
谢谢,理查德。我的表格有动态和未知列。 – 2013-05-06 13:30:46
嗨,bluefeet!你刚刚解决了这个问题。非常感谢! – 2013-05-06 13:29:06
这是真棒..做魔术:) – UberNeo 2015-02-16 14:23:53