本文介绍了查询交叉表视图的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在PostgreSQL中有一个如下表:
I have a table in PostgreSQL like below:
--------------------------------------------------------------
Item1 | Item2 | Item3 | Item4 |Value1| Value2| Value3| Value4|
--------------------------------------------------------------
我要查询将显示如下表:
I want a query which will show this table like below:
ItemHead| ValueHead
---------------
Item1 | Value1|
---------------
Item2 | Value2|
----------------
Item3 | Value3|
----------------
Item4 | Value4|
---------------
推荐答案
使用单 SELECT
和 LATERAL
加入 VALUES
表达式。这比多个 SELECT
语句更短,更快:
Use a single SELECT
with a LATERAL
join to a VALUES
expression. That's shorter and faster than multiple SELECT
statements:
SELECT v.*
FROM tbl, LATERAL (
VALUES
(item1, value1)
, (item2, value2) -- data types must be compatible
, (item3, value3)
, (item4, value4)
) v ("ItemHead", "ValueHead"); -- your desired column names
相关:
- Convert one row into multiple rows with fewer columns
- SELECT DISTINCT on multiple columns
- Postgres: convert single row to multiple rows
注意:您添加了标签的问题。但是Postgres函数 crosstab()
来自其他用于透视,而此任务与此相反,有时称为保持不变。相关:
Note: You added the tag crosstab. But the Postgres function crosstab()
from the additional tablefunc
module is used for pivoting, while this task is the opposite, sometimes referred to as unpivoting. Related:
- PostgreSQL Crosstab Query
这篇关于查询交叉表视图的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!