返回列名称和不同的值 [英] Return column name and distinct values

问题描述

说我在postgres中有一个简单的表格，如下所示：

Say I have a simple table in postgres as the following:

+--------+--------+----------+
|  Car   |  Pet   |   Name   |
+--------+--------+----------+
| BMW    |  Dog   |   Sam    |
| Honda  |  Cat   |   Mary   |
| Toyota |  Dog   |   Sam    |
| ...    |  ...   |   ...    |

我想运行一个SQL查询，该查询可以返回第一列中的列名和唯一值在第二栏中。例如：

I would like to run a sql query that could return the column name in the first column and unique values in the second column. For example:

+--------+--------+
|  Col   |  Vals  |
+--------+--------+
| Car    |  BMW   |
| Car    | Toyota |
| Car    | Honda  |
| Pet    |  Dog   |
| Pet    |  Cat   |
| Name   |  Sam   |
| Name   |  Mary  |
| ...    |  ...   |

：

I found a bit of code that can be used to return all of the unique values from multiple fields into one column:

-- Query 4b.  (104 ms, 128 ms)
select distinct unnest( array_agg(a)||
                        array_agg(b)||
                        array_agg(c)||
                        array_agg(d) )
from t ;

但是我不太了解代码，以至于不知道如何将列名附加到另一列中

But I don't understand the code well enough to know how to append the column name into another column.

我还找到了一个查询，该查询可以返回表中的列名。也许是它的子查询与上面显示的查询4b结合使用？

I also found a query that can return the column names in a table. Maybe a sub-query of this in combination with the "Query 4b" shown above?

推荐答案

SQL Fiddle

SELECT distinct
       unnest(array['car', 'pet', 'name']) AS col,
       unnest(array[car, pet, name]) AS vals
FROM t
order by col

这篇关于返回列名称和不同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！