将行折叠为一列 [英] Collapse rows into one column

问题描述

我有一张桌子，其布局如下:

I have a table with the following layout:

 ID   Label   Value
 --   -----   -----
 1    Lab1    Value1-1
 1    Lab2    Value1-2
 1    Lab3    Value1-3
 1    Lab4    Value1-4
 1    Lab5    Value1-5
 1    Lab6    Value1-6
 2    Lab1    Value2-1
 2    Lab2    Value2-2
 2    Lab3    Value2-3
 2    Lab4    Value2-4
 2    Lab5    Value2-5
 2    Lab6    Value2-6
 ...

我想将表转换为如下布局:

I'd like to convert the table to be laid out as follows:

 ID   Lab1      Lab2       Lab3       Lab4       Lab5       Lab6
 1    Value1-1  Value1-2   Value1-3   Value1-4   Value1-5   Value1-6
 2    Value2-1  Value2-2   Value2-3   Value2-4   Value2-5   Value2-6
 ...

我在PostgreSQL中使用SQL.有没有一种简单的方法(且内存效率高)来做到这一点? 我看过一些提到使用数据透视的帖子，但是我不确定这是否行得通，而且我看到的描述似乎是特定于Oracle的.

I'm using SQL in PostgreSQL. Is there an easy (and memory efficient) way to do this? I've seen some posts that mention using pivots, but I'm not sure if that would work and the descriptions I saw appeared to be specific to Oracle.

推荐答案

首先安装扩展

First install the extension tablefunc, if you haven't already. Needs to be done once per database.

CREATE EXTENSION tablefunc;

对于CREATE EXTENSION，您需要PostgreSQL 9.1 .在旧版本中，您必须使用以下命令从外壳运行安装脚本:

You need PostgreSQL 9.1 for CREATE EXTENSION. In older versions you have to run the install script from the shell with a command like:

psql -d dbname -f SHAREDIR/contrib/tablefunc.sql

Postgres 9.0中的详细信息.

然后，您可以使用像这样的查询:

Then you can use a query like this one:

SELECT *
FROM   crosstab (
    'SELECT id
           ,label
           ,value
     FROM   t
     ORDER  BY 1, 2',

    'SELECT DISTINCT label
     FROM   t
     ORDER  BY 1')
AS tbl (
 id   int
,lab1 text
,lab2 text
,lab3 text
,lab4 text
,lab5 text
,lab6 text
);

完全返回您的要求.
您也可以为此创建一个函数.我在与此密切相关的答案中添加了更多信息.

Returns exactly what you asked for.
You can also create a function for that. I added more information in this closely related answer.

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