PostgreSQL将列转换为行？移调？

CREATE TABLE tbl (
   sl_no int,username text,designation text,salary int
);

每行都会返回一个新的列.使用这样的动态返回类型,通过对数据库的单一调用,几乎不可能使其完全动态.演示解决方案有两个步骤：

>生成查询
>执行生成的查询

一般来说,这受到表可以容纳的最大列数限制.所以对于超过1600行(或更少)的表,不是一个选项.细节：

> What is the maximum number of columns in a PostgreSQL select query

Postgres 9.3以上

交叉表()的动态解决方案

>完全动态,适用于任何表.在两个地方提供表名：

SELECT 'SELECT *
FROM   crosstab(
       ''SELECT unnest(''' || quote_literal(array_agg(attname))
                           || '''::text[]) AS col,row_number() OVER (),unnest(ARRAY[' || string_agg(quote_ident(attname)
                              || '::text',',') || ']) AS val
        FROM   ' || attrelid::regclass || '
        ORDER  BY generate_series(1,' || count(*) || '),2''
   ) t (col text,'
     || (SELECT string_agg('r'|| rn ||' text',')
         FROM (SELECT row_number() OVER () AS rn FROM tbl) t)
     || ')' AS sql
FROM   pg_attribute
WHERE  attrelid = 'tbl'::regclass
AND    attnum > 0
AND    NOT attisdropped
GROUP  BY attrelid;

可以用一个参数包装成一个函数…
生成一个表单查询：

SELECT *
FROM   crosstab(
       'SELECT unnest(''{sl_no,username,designation,salary}''::text[]) AS col,unnest(ARRAY[sl_no::text,username::text,designation::text,salary::text]) AS val
        FROM   tbl
        ORDER  BY generate_series(1,4),2'
   ) t (col text,r1 text,r2 text,r3 text,r4 text)

产生所需结果：

col         r1    r2      r3     r4
-----------------------------------
sl_no       1      2      3      4
username    A      B      C      D
designation XYZ    RTS    QWE    HGD
salary      10000  50000  20000  34343

简单的解决方案

SELECT 'SELECT unnest(''{sl_no,salary}''::text[] AS col),' || string_agg('unnest('
                    || quote_literal(ARRAY[sl_no::text,salary::text])
                    || '::text[]) AS row' || sl_no,E'n,') AS sql
FROM   tbl;

对于超过两列的表,缓慢.

生成一个表单查询：

SELECT unnest('{sl_no,salary}'::text[]) AS col,unnest('{10,Joe,Music,1234}'::text[]) AS row1,unnest('{11,Bob,Movie,2345}'::text[]) AS row2,unnest('{12,Dave,Theatre,2356}'::text[]) AS row3,unnest('{4,D,HGD,34343}'::text[]) AS row4

相同的结果.

Postgres 9.4

交叉表()的动态解决方案

如果可以,请使用.打败其他人

SELECT 'SELECT *
FROM   crosstab(
       $ct$SELECT u.attnum,t.rn,u.val
        FROM  (SELECT row_number() OVER () AS rn,* FROM '
                              || attrelid::regclass || ') t,') || '])
                 WITH ORDINALITY u(val,attnum)
        ORDER  BY 1,2$ct$
   ) t (attnum bigint,')
         FROM  (SELECT row_number() OVER () AS rn FROM tbl) t)
     || ')' AS sql
FROM   pg_attribute
WHERE  attrelid = 'tbl'::regclass
AND    attnum > 0
AND    NOT attisdropped
GROUP  BY attrelid;

使用attnum而不是实际的列名.更简单快捷再次将结果加入pg_attribute或集成列名称,如第9.3节示例.
生成一个表单查询：

SELECT *
FROM   crosstab(
       $ct$SELECT u.attnum,* FROM tbl) t,salary::text])
                WITH ORDINALITY u(val,r4 text);

这使用了一系列高级功能.太多了解不了

简单的解决方案

一个unnest()现在可以并行地将多个数组并入不及格.

SELECT 'SELECT * FROM unnest(
  ''{sl_no,salary}''::text[],' || string_agg(quote_literal(ARRAY[sl_no::text,salary::text])
              || '::text[]',')
    || E') n AS t(col,' || string_agg('row' || sl_no,') || ')' AS sql
FROM   tbl;

结果：

SELECT * FROM unnest(
 '{sl_no,salary}'::text[],'{10,1234}'::text[],'{11,2345}'::text[],'{12,2356}'::text[])
 AS t(col,row1,row2,row3,row4)

SQL Fiddle在第9.3节运行.