将列值设置为 SQL 查询结果中的列名

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

我想读取一个表，该表的值将是 sql 查询结果的列名.例如，我将 table1 设为 ..

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 ..

id    col1     col2
----------------------
0      name    ax
0      name2   bx
0      name3   cx
1      name    dx
1      name2   ex
1      name3   fx

如果您看到 id = 0，则 name 的值为 ax，name2 为 bx，name3 为 cx.将列显示为 id、name、name2、name3 会更容易，而不是行.现在我希望查询的结果如下所示:

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   name    name2     name3
0    ax      bx         cx
1    dx      ex         fx

有人可以帮助我实现这一目标吗?

Can someone help me in achieving this?

推荐答案

这是通过数据透视表完成的.按 id 分组，您为要在列中捕获的每个值发出 CASE 语句，并使用类似 MAX() 聚合来消除空值并折叠到一行.

This is done with a pivot table. Grouping by id, you issue CASE statements 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.

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

这是一个工作示例

注意:这仅适用于 col1 的有限且已知数量的可能值.如果可能值的数量未知，则需要在循环中动态构建 SQL 语句.

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.

相关文章