将列标题转置为PostgreSQL中的行 [英] transpose column headers to rows in postgresql

问题描述

我有一个类似这样的视图

I have a view which looks like this

          value1count     value2count value3count
          ----------------------------------------
             25              35          55

我需要将列标题转置为行，因此需要使其看起来像

I need to transpose the column header into rows and so I need it to look like

          Values              Count
         -----------------------------
           value1count         25
           value2count         35
           value3count         55

我可以通过选择单个列名来实现作为第一列，将数据作为第二列，然后对所有列进行相同的并集。

I can do this by selecting individual column names as first column and data as second column and then do a union of the same for all columns.

是否有更好的方法？
我正在使用PosgreSQL 8.1，因此没有可操作的数据透视运算符。

Is there a better way to do this? I am using PosgreSQL 8.1 and so don't have pivot operators to work with.

感谢您的预先答复。

推荐答案

Crosstab仅能满足您的需求，但这应该可以帮助您：

Crosstab only does the reverse of what you need, but this should help you:

第一创建8.4中包含的 unnest（）函数，请参见此处以获取说明。

First create the unnest() function that is included in 8.4, see here for instructions.

然后您可以执行此操作（基于此帖子）：

Then you can do this (based on this post):

SELECT
   unnest(array['value1Count', 'value2Count', 'value3Count']) AS "Values",
   unnest(array[value1Count, value2Count, value3Count]) AS "Count"
FROM view_name
ORDER BY "Values"

我可以验证它在8.4中是否有效，但是因为我没有8.1，所以我无法保证它会一样工作。

I can verify that this works in 8.4, but because I don't have 8.1, I can't promise it will work the same.

这篇关于将列标题转置为PostgreSQL中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！