本文介绍了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.
这篇关于MySQL中的行到列转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！