MySQL 将列值设置为 SQL 查询结果中的列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12808189/
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
Setting column values as column names in the SQL query result
提问by ravi
I wanted to read a table which has values which will be the column names of the sql query result. For example, I have table1 as ..
我想读取一个表，该表的值将是 sql 查询结果的列名。例如，我有 table1 作为 ..
id col1 col2
----------------------
0 name ax
0 name2 bx
0 name3 cx
1 name dx
1 name2 ex
1 name3 fx
If you see for id = 0, name has value of ax and name2 is bx and name3 is cx. Instead of this being rows it would be easier to show columns as id, name, name2, name3. Now I want the result of the query to look like this:
如果您看到 id = 0，则 name 的值为 ax，name2 为 bx，name3 为 cx。将列显示为 id、name、name2、name3 会更容易，而不是行。现在我希望查询的结果如下所示：
id name name2 name3
0 ax bx cx
1 dx ex fx
Can someone help me in achieving this?
有人可以帮助我实现这一目标吗？
回答by Michael Berkowski
This is done with a pivot table. Grouping by id, you issue CASEstatements for each value you want to capture in a column and use something like a MAX()aggregate to eliminate the nulls and collapse down to one row.
这是通过数据透视表完成的。按 分组id，您可以CASE为要在列中捕获的每个值发出语句，并使用诸如MAX()聚合之类的东西来消除空值并将其折叠为一行。
SELECT
id,
/* if col1 matches the name string of this CASE, return col2, otherwise return NULL */
/* Then, the outer MAX() aggregate will eliminate all NULLs and collapse it down to one row per id */
MAX(CASE WHEN (col1 = 'name') THEN col2 ELSE NULL END) AS name,
MAX(CASE WHEN (col1 = 'name2') THEN col2 ELSE NULL END) AS name2,
MAX(CASE WHEN (col1 = 'name3') THEN col2 ELSE NULL END) AS name3
FROM
yourtable
GROUP BY id
ORDER BY id
Here's a working sample
这是一个工作示例
Note: This only works as is for a finite and known number of possible values for col1. If the number of possible values is unknown, you need to build the SQL statement dynamically in a loop.
注意：这仅适用于 的有限且已知数量的可能值col1。如果可能值的数量未知，则需要在循环中动态构建 SQL 语句。
回答by Taryn
What you are attempting to do is a PIVOTMySQL does not have a PIVOTfunction so you can replicate this using a CASEand an aggregate function.
您尝试做的是PIVOTMySQL 没有PIVOT函数，因此您可以使用 aCASE和聚合函数复制它。
If you have a known number of columns, then you can use a static version and hard-code the values. Similar to this (See SQL Fiddle with demo):
如果您有已知数量的列，那么您可以使用静态版本并对值进行硬编码。与此类似（请参阅SQL Fiddle with demo）：
select id,
max(case when col1='name' then col2 end) name,
max(case when col1='name2' then col2 end) name2,
max(case when col1='name3' then col2 end) name3
from yourtable
group by id
But if you have an unknown number of columns, then you can use a prepared statement and create this dynamically:
但是如果您有未知数量的列，那么您可以使用准备好的语句并动态创建它：
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'max(case when col1 = ''',
col1,
''' then col2 end) AS ',
col1
)
) INTO @sql
FROM yourtable;
SET @sql = CONCAT('SELECT id, ', @sql, '
FROM yourtable
GROUP BY id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
回答by Shaneshwar Makone
select id,
max(if(tablename.columnname = 'name',tablename.columnname,null)) as namealise,
max(if(tablename.columnname = 'name1',tablename.columnname,null)) as namealise1
from table1, table2
where table1.id = table2.id
group by table1.id
order by table1.id
