返回列名称和不同的值
问题描述:
说我在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?
答
SELECT distinct
unnest(array['car', 'pet', 'name']) AS col,
unnest(array[car, pet, name]) AS vals
FROM t
order by col