PostgreSQL 行到列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9589217/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL row to columns
提问by user1252871
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:
我正在尝试创建一个动态系统，允许用户从 Excel 导入数据列表，因此我需要有动态列，例如：
custom_columns_table
id list_id data_type column_name data ....
1 1 VARCHAR(255) email [email protected] ....
2 1 VARCHAR(255) name Jhon ....
list_table
id
1
I need a result like this:
我需要这样的结果：
id email name ....
1 [email protected] 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?
有谁知道我该怎么做？
回答by Erwin Brandstetter
First off, the crosstab()family of functions is not installed in standard PostgreSQL. You need to install the extension tablefuncfor this. In PostgreSQL 9.1 you would simply:
首先，crosstab()函数系列没有安装在标准的 PostgreSQL 中。您需要为此安装扩展tablefunc。在 PostgreSQL 9.1 中，您只需：
CREATE EXTENSION tablefunc;
For older versions have a look at this related answer.
对于旧版本，请查看此相关答案。
Query
询问
The query could look like this:
查询可能如下所示：
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
);
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 NULLfor the emailcolumn. Detailed explanation:
我使用crosstab()带有两个参数的形式，因为这允许缺少属性。就像，当一个人没有电子邮件时。然后此表单将返回NULL该email列。详细解释：
Function
功能
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;
Call:
称呼：
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')
