MySQL中的行到列转换

2021-11-20 00:00:00 pivot-table mysql

我在 MySQL 中设置了这个结果:

I have this result set in MySQL :

ID          Type Email          Degignation  
1000000000  202 brijesh@abc.com Entrepreneur
1000000000  234 brijesh@abc.com Engineering,Development
1000000000  239 brijesh@abc.com CTO

我有很多这样的元组，不仅仅是三个.我希望 Type 为列，最后一列成为它们的行值.如下图

I have many such tuples not only three . I want Type to be column and last column to become row value for them . Like below

ID                202         234                     239        Email 
1000000000   Entrepreneur  Engineering,Development    CTO       brijesh@abc.com

推荐答案

这称为数据透视表.制作有点尴尬:

This is called a pivot table. It's kind of awkward to produce:

SELECT ID, 
 MAX(CASE Type WHEN 202 THEN Degignation END) AS `202`
 MAX(CASE Type WHEN 234 THEN Degignation END) AS `234`
 MAX(CASE Type WHEN 239 THEN Degignation END) AS `239`
 Email
FROM mytable
GROUP BY ID, Email

请注意，在编写查询之前，您必须知道所有不同的 Type 值.SQL 不允许结果集在发现表中的数据值时动态添加更多列.列必须在查询准备时固定.

Note that you must know all the distinct Type values before you write the query. SQL doesn't allow a result set to add more columns dynamically as it discovers data values in the table. Columns must be fixed at query prepare time.

相关文章