PostgreSQL行到列 [英] PostgreSQL row to columns

问题描述

我正在尝试创建一个动态系统，允许用户从Excel导入数据列表，因此我需要具有动态列，例如：

I'm trying to create a dynamic system that allows the users to import lists of data from Excel so I need to have dynamic columns, ex:

custom_columns_table
id   list_id  data_type       column_name  data              ....
1    1        VARCHAR(255)    email        [e-mail]  ....
2    1        VARCHAR(255)    name         Jhon              ....

list_table
id
1

我需要这样的结果：

id email             name  ....
1  [e-mail]  Jhon  ....

我发现了一些使用交叉表的示例，但我不知道

I have found some examples using crosstab but I don`t know if it will work in this case.

有人知道我该怎么做吗？

Does anyone know how can I do this?

推荐答案

首先， crosstab（）函数系列未安装在标准PostgreSQL中。您需要安装扩展 tablefunc 为此。在PostgreSQL 9.1中，您将简单地：

First off, the crosstab() family of functions is not installed in standard PostgreSQL. You need to install the extension tablefunc for this. In PostgreSQL 9.1 you would simply:

CREATE EXTENSION tablefunc;

对于较旧的版本，请查看此相关答案。

For older versions have a look at this related answer.

查询看起来像

SELECT *
FROM   crosstab (
        'SELECT l.id
               ,c.column_name
               ,c.data
         FROM   custom_columns_table c
         JOIN   list_table l ON l.id = c.list_id
         ORDER  BY 1',

        'SELECT DISTINCT column_name
         FROM   custom_columns_table
         ORDER  BY 1')
AS tbl (
    id integer
   ,email text
   ,name text
   );

我使用 crosstab（）的形式有两个参数，因为这允许缺少属性。例如，当一个人没有电子邮件时。然后，此表单将为 email 列返回 NULL 。详细说明：

I use the form of crosstab() with two parameters, because that allows for missing attributes. Like, when a person has no email. Then this form will return NULL for the email column. Detailed explanation:

• PostgreSQL交叉表查询

• PostgreSQL Crosstab Query

或创建一个函数，这样就不必为每次调用都提供列定义列表：

Or create a function so you don't have to supply a column definition list for every call:

CREATE OR REPLACE FUNCTION f_mycross(text, text)
  RETURNS TABLE (
    id integer
   ,email text
   ,name text)
  AS '$libdir/tablefunc','crosstab_hash' LANGUAGE C STABLE STRICT;

致电：

SELECT * FROM f_mycross(
       'SELECT l.id
              ,c.column_name
              ,c.data
        FROM   custom_columns_table c
        JOIN   list_table l ON l.id = c.list_id
        ORDER  BY 1',

       'SELECT DISTINCT column_name
        FROM   custom_columns_table
        ORDER  BY 1')

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