选择行单元格作为新列 [英] Select row cells as new columns

问题描述

基本上，我有一个表，该表存储具有某些限制的列名称:infos，而另一个表存储这些列的值:info_data.我想获得一个表，其中包含来自infos的列和来自info_data的数据.我已经尝试使用交叉表功能，但效果不理想.

Basically, I have a table that stores column names with some restrictions: infos, and another one that stores the values for those columns: info_data. I want to get a table which has the columns from infos and data from info_data. I've tried with crosstab function but it doesn't have the desired effect.

我有2张桌子:

CREATE TABLE infos
(id serial PRIMARY KEY,
 name text NOT NULL,
 id_member integer NOT NULL,
 title text,
 min_length integer NOT NULL DEFAULT 0,
 max_length integer NOT NULL DEFAULT 30,
 required boolean NOT NULL DEFAULT false,
 type text NOT NULL DEFAULT 'text'::text
);

CREATE INDEX info_id_idx ON infos (id);

和

CREATE TABLE info_data
(id serial PRIMARY KEY,
 id_info integer,
 value text,
  CONSTRAINT info_data_id_info_fkey FOREIGN KEY (id_info)
      REFERENCES infos (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE INDEX info_data_id_idx ON info_data(id);

具有以下值:

信息:

COPY infos (id, name, id_member, title, min_length, max_length, required, type)     FROM     stdin;
1 nume 1 Nume 0 30 t text
2 prenume 1 Prenume 0 30 t text
3 cnp 1 C.N.P. 13 13 t number
4 nume anterior 1 Nume anterior 0 30 f text
5 stare civila 1 Starea civila 0 30 f text
6 cetatenie 1 Cetatenie 0 30 f text
7 rezidenta 1 Rezidenta 0 30 f text
9 tip act 1 C.I. / B.I. 0 10 t text
10 serie ci 1 Serie C.I. / B.I. 0 30 t text
11 numar ci 1 Numar C.I. / B.I. 0 30 t text
12 data eliberarii 1 Data eliberarii 0 30 t text
13 eliberat de 1 Eliberat de 0 30 t text
8 adresa 1 Adresa 0 50 f text
\.

info_data:

info_data:

COPY info_data (id, id_info, value) FROM stdin;
1 1 a
2 2 a
3 3 100
4 4
5 5
6 6
7 7
8 8
9 9 ci
10 10 sv
11 11 13
12 12 132
13 13 123
14 1 b
15 2 b
16 3 100
17 4
18 5
19 6
20 7
21 8
22 9 BI
23 10 XT
24 11 123
25 12 10
26 13 10
\.

问题: 如何获得此输出? (这些列必须根据infos表中的唯一条目

The question: How can I achieve this output? (the columns have to be generated based upon the unique entries from the infos table

nume, prenume, cnp, nume anterior, ... (as columns - built from infos)
a   , a, ...
b   , b, ... (as rows - built from info_data)

推荐答案

这个问题比您预期的要难得多 .您尝试使用crosstab()的目的是朝着正确的方向.但是要分配动态列名，您还需要动态SQL: EXECUTE在plpgsql函数中.

This question was a lot harder to solve than you may have expected. Your attempt with crosstab() was aiming in the right direction. But to assign dynamic column names you need dynamic SQL in addition: EXECUTE in a plpgsql function.

将列infos.type的数据类型从text更改为 numeric ，因此它可以正常工作

Change the data type of the column infos.type from text to regtype to prevent SQL injection and other errors. For instance, you have the data type number, which is not a valid PostgreSQL data type. I replaced it with numeric, so it can work.

您可以通过避免需要双引号的列名来简化任务.类似于nume_anterior而不是"nume anterior".

You could simplify the task by avoiding column names that need double-quoting. Like nume_anterior instead of "nume anterior".

您可能想在表info_data中添加列row_id，以标记一行的所有元素. crosstab()函数需要它，它允许您忽略具有NULL值的列.具有两个参数的crosstab()函数可以处理缺少的列.我用下面的表达式(d.id-1)/13合成了缺少的列-适用于您示例中的数据.

You might want to add a column row_id to your table info_data to mark all elements of one row. You need it for the crosstab() function, and it allows you to ignore columns with NULL values. The crosstab() function with two parameters can deal with missing columns. I synthesize the missing column with the expression (d.id-1)/13 below - which works for the data in your example.

您需要安装附加模块tablefunc (每次数据库):

You need to install the additional module tablefunc (once per database):

CREATE EXTENSION tablefunc;

找到 此相关答案中的其他说明和链接 .

此功能将执行所需的操作:

This function will do what are looking for:

CREATE OR REPLACE FUNCTION f_mytbl()
  RETURNS TABLE (id int
, nume text           , prenume text       , cnp numeric
, "nume anterior" text, "stare civila" text, cetatenie text
, rezidenta text      , adresa text        , "tip act" text
, "serie ci" text     , "numar ci" text    , "data eliberarii" text
, "eliberat de" text)
  LANGUAGE plpgsql AS
$BODY$
BEGIN

RETURN QUERY EXECUTE $f$
SELECT *
FROM   crosstab(
    'SELECT (d.id-1)/13 -- AS row_id
          , i.id, d.value
     FROM   infos i
     JOIN   info_data d ON d.id_info = i.id
     ORDER  BY 1, i.id',

    'SELECT id
     FROM   infos
     ORDER  BY id'
    )
AS tbl ($f$ || 'id int,
, nume text           , prenume text       , cnp numeric
, "nume anterior" text, "stare civila" text, cetatenie text
, rezidenta text      , adresa text        , "tip act" text
, "serie ci" text     , "numar ci" text    , "data eliberarii" text
, "eliberat de" text)';

END;
$BODY$;

致电:

SELECT * FROM x.mytbl();

不要被嵌套的美元报价.

顺便说一句:我使用以下语句创建了列列表:

BTW: I created the column list with this statement:

SELECT 'id int,' || string_agg(quote_ident(name) || ' ' || type
                              ,', ' ORDER BY i.id) 
FROM   infos i;

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