MySQL SQL - 如何转置？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3392956/
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
SQL - How to transpose?
提问by StackOverflowNewbie
I have something similar to the following table:
我有类似于下表的内容：
================================================
| Id | UserId | FieldName | FieldValue |
=====+========+===============+================|
| 1 | 100 | Username | John Doe |
|----+--------+---------------+----------------|
| 2 | 100 | Password | pass123! |
|----+--------+---------------+----------------|
| 3 | 102 | Username | Jane |
|----+--------+---------------+----------------|
| 4 | 102 | Password | $ecret |
|----+--------+---------------+----------------|
| 5 | 102 | Email Address | [email protected] |
------------------------------------------------
I need a query that will give me a result like this:
我需要一个查询，它会给我这样的结果：
==================================================
| UserId | Username | Password | Email Address |
=========+===========+===========================|
| 100 | John Doe | pass123! | |
|--------+-----------+----------+----------------|
| 102 | Jane | $ecret | [email protected] |
|--------+-----------+----------+----------------|
Note that the values in FieldName are not limited to Username, Password, and Email Address. They can be anything as they are user defined.
请注意，FieldName 中的值不限于用户名、密码和电子邮件地址。它们可以是用户定义的任何东西。
Is there a way to do this in SQL?
有没有办法在 SQL 中做到这一点？
回答by OMG Ponies
MySQL doesn't support ANSI PIVOT/UNPIVOT syntax, so that leave you to use:
MySQL 不支持 ANSI PIVOT/UNPIVOT 语法，因此您可以使用：
SELECT t.userid
MAX(CASE WHEN t.fieldname = 'Username' THEN t.fieldvalue ELSE NULL END) AS Username,
MAX(CASE WHEN t.fieldname = 'Password' THEN t.fieldvalue ELSE NULL END) AS Password,
MAX(CASE WHEN t.fieldname = 'Email Address' THEN t.fieldvalue ELSE NULL END) AS Email
FROM TABLE t
GROUP BY t.userid
As you can see, the CASE statements need to be defined per value. To make this dynamic, you'd need to use MySQL's Prepared Statement (dynamic SQL) syntax.
如您所见，需要为每个值定义 CASE 语句。要使其动态化，您需要使用MySQL 的 Prepared Statement (dynamic SQL) 语法。
回答by Sam Saffron
You could use GROUP_CONCAT
你可以使用GROUP_CONCAT
(untested)
（未经测试）
SELECT UserId,
GROUP_CONCAT( if( fieldname = 'Username', fieldvalue, NULL ) ) AS 'Username',
GROUP_CONCAT( if( fieldname = 'Password', fieldvalue, NULL ) ) AS 'Password',
GROUP_CONCAT( if( fieldname = 'Email Address', fieldvalue, NULL ) ) AS 'Email Address',
FROM table
GROUP BY UserId
