sql - 返回列名和不同的值

Question

假设我在 postgres 中有一个简单的表，如下所示:

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

我想运行一个 sql 查询，它可以在第一列中返回列名，在第二列中返回唯一值。例如:

+--------+--------+
|  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 ;

但是我对代码的理解不够好，不知道如何将列名附加到另一列中。

I also found a query that can return the column names in a table.也许这个子查询与上面显示的“查询 4b”相结合？

Answer 1

SQL Fiddle

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