将列值设置为SQL查询结果中的列名 [英] Setting column values as column names in the SQL query result

问题描述

我想读取一个表，该表的值将是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.

这篇关于将列值设置为SQL查询结果中的列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！