MySQL中的行到列转换 [英] Row to column transformation in MySQL

问题描述

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

I have this result set in MySQL :

ID          Type Email          Degignation  
1000000000  202 [e-mail] Entrepreneur
1000000000  234 [e-mail] Engineering,Development
1000000000  239 [e-mail] 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       [e-mail]

推荐答案

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

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.

这篇关于MySQL中的行到列转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！