本文介绍了PostgreSQL 将列转换为行?转置?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个 PostgreSQL 函数(或表)，它给了我以下输出:
Sl.no username 指定工资等..1 A XYZ 10000 ...2 B RTS 50000 ...3 C QWE 20000 ...4 D HGD 34343 ...现在我想要输出如下:
Sl.no 1 2 3 4 ...用户名 A B C D ...名称 XYZ RTS QWE HGD ...薪资 10000 50000 20000 34343 ...如何做到这一点?
解决方案
根据表格形式回答:
CREATE TABLE tbl (sl_no int, 用户名文本, 指定文字, 工资整数);每行产生一个要返回的新列.对于像这样的动态返回类型，几乎不可能通过对数据库的一次调用使其完全动态化.通过两步演示解决方案:
- 生成查询
- 执行生成的查询
通常，这受表可以容纳的最大列数限制.所以对于超过 1600 行(或更少)的表来说不是一个选项.详情:
Postgres 9.3 或更早版本
使用crosstab()
的动态解决方案- 完全动态，适用于任何表格.在两个位置提供表名:
SELECT 'SELECT *从交叉表(''SELECT unnest(''' || quote_literal(array_agg(attname))||'''::text[]) AS col, row_number() OVER(), unnest(ARRAY[' || string_agg(quote_ident(attname)||'::文本', ',') ||']) 作为 val从'||attrelid::regclass ||'ORDER BY generate_series(1,' || count(*) || '), 2'') t (col 文本, '||(SELECT string_agg('r'|| rn ||' text', ',')FROM (SELECT row_number() OVER () AS rn FROM tbl) t)||')' AS sqlFROM pg_attributeWHERE attrelid = 'tbl'::regclassAND 属性 > 0并且没有被拒绝GROUP BY attrelid;可以包装成一个带有单个参数的函数...
生成以下形式的查询:
SELECT *从交叉表('SELECT unnest(''{sl_no,username,designation,salary}''::text[]) AS col, row_number() OVER(), unnest(ARRAY[sl_no::text,username::text,designation::text,salary::text]) AS val从表ORDER BY generate_series(1,4), 2') t (col 文本、r1 文本、r2 文本、r3 文本、r4 文本)产生想要的结果:
col r1 r2 r3 r4---------------------sl_no 1 2 3 4用户名 A B C D名称 XYZ RTS QWE HGD工资 10000 50000 20000 34343使用 unnest() 的简单解决方案
SELECT 'SELECT unnest(''{sl_no, username, designation, salary}''::text[] AS col), ' ||string_agg('取消嵌套('||报价文字(数组[sl_no::text, username::text, designation::text,salary::text])||'::text[]) AS 行' ||sl_no, E'
, ') AS sql从 tbl;- 对于多于几列的表，速度较慢.
生成以下形式的查询:
SELECT unnest('{sl_no, username, designation, 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+
使用crosstab()
的动态解决方案如果可以，请使用它.击败其他人.
SELECT 'SELECT *从交叉表($ct$SELECT u.attnum, t.rn, u.valFROM (SELECT row_number() OVER () AS rn, * FROM '||attrelid::regclass ||') t, unnest(ARRAY[' || string_agg(quote_ident(attname)||'::文本', ',') ||'])有序数 u(val, attnum)按 1, 2$ct$ 订购) t (attnum bigint, '||(SELECT string_agg('r'|| rn ||' text', ', ')FROM (SELECT row_number() OVER () AS rn FROM tbl) t)||')' AS sqlFROM pg_attributeWHERE attrelid = 'tbl'::regclassAND 属性 > 0并且没有被拒绝GROUP BY attrelid;使用 attnum 而不是实际的列名进行操作.更简单更快.再次将结果连接到 pg_attribute 或像 pg 9.3 示例中那样集成列名称.
生成以下形式的查询:
SELECT *从交叉表($ct$SELECT u.attnum, t.rn, u.valFROM (SELECT row_number() OVER () AS rn, * FROM tbl) t, unnest(ARRAY[sl_no::text,username::text,designation::text,salary::text])有序数 u(val, attnum)按 1, 2$ct$ 订购) t (attnum bigint, r1 文本, r2 文本, r3 文本, r4 文本);这使用了一系列高级功能.太多了，无法解释.
使用 unnest() 的简单解决方案
一个 unnest() 现在可以并行取消嵌套多个数组.
SELECT 'SELECT * FROM unnest(''{sl_no, 用户名，职称，薪水}''::text[], ' ||string_agg(quote_literal(ARRAY[sl_no::text, username::text, designation::text,salary::text])||'::text[]', E'
, ')||E')
AS t(col,' || string_agg('row' || sl_no, ',') || ')' AS sql从 tbl;结果:
SELECT * FROM unnest('{sl_no, 用户名，职称，薪水}'::text[],'{10,Joe,Music,1234}'::text[],'{11,Bob,Movie,2345}'::text[],'{12,Dave,Theatre,2356}'::text[])AS t(col,row1,row2,row3,row4)SQL Fiddle 在 pg 9.3 上运行.>
I have a PostgreSQL function (or table) which gives me the following output:
Sl.no username Designation salary etc..
1 A XYZ 10000 ...
2 B RTS 50000 ...
3 C QWE 20000 ...
4 D HGD 34343 ...
Now I want the Output as below:
Sl.no 1 2 3 4 ...
Username A B C D ...
Designation XYZ RTS QWE HGD ...
Salary 10000 50000 20000 34343 ...
How to do this?
解决方案
Basing my answer on a table of the form:
CREATE TABLE tbl (
sl_no int
, username text
, designation text
, salary int
);
Each row results in a new column to return. With a dynamic return type like this, it's hardly possible to make this completely dynamic with a single call to the database. Demonstrating solutions with two steps:
- Generate query
- Execute generated query
Generally, this is limited by the maximum number of columns a table can hold. So not an option for tables with more than 1600 rows (or fewer). Details:
Postgres 9.3 or older
Dynamic solution with crosstab()
- Completely dynamic, works for any table. Provide the table name in two places:
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;Could be wrapped into a function with a single parameter ...
Generates a query of the form:
SELECT *
FROM crosstab(
'SELECT unnest(''{sl_no,username,designation,salary}''::text[]) AS col
, row_number() OVER ()
, 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)
Produces the desired result:
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
Simple solution with unnest()
SELECT 'SELECT unnest(''{sl_no, username, designation, salary}''::text[] AS col)
, ' || string_agg('unnest('
|| quote_literal(ARRAY[sl_no::text, username::text, designation::text, salary::text])
|| '::text[]) AS row' || sl_no, E'
, ') AS sql
FROM tbl;
- Slow for tables with more than a couple of columns.
Generates a query of the form:
SELECT unnest('{sl_no, username, designation, 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
Same result.
Postgres 9.4+
Dynamic solution with crosstab()
Use this if you can. Beats the rest.
SELECT 'SELECT *
FROM crosstab(
$ct$SELECT u.attnum, t.rn, u.val
FROM (SELECT row_number() OVER () AS rn, * FROM '
|| attrelid::regclass || ') t
, unnest(ARRAY[' || string_agg(quote_ident(attname)
|| '::text', ',') || '])
WITH ORDINALITY u(val, attnum)
ORDER BY 1, 2$ct$
) t (attnum bigint, '
|| (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;Operating with attnum instead of actual column names. Simpler and faster. Join the result to pg_attribute once more or integrate column names like in the pg 9.3 example.
Generates a query of the form:
SELECT *
FROM crosstab(
$ct$SELECT u.attnum, t.rn, u.val
FROM (SELECT row_number() OVER () AS rn, * FROM tbl) t
, unnest(ARRAY[sl_no::text,username::text,designation::text,salary::text])
WITH ORDINALITY u(val, attnum)
ORDER BY 1, 2$ct$
) t (attnum bigint, r1 text, r2 text, r3 text, r4 text);
This uses a whole range of advanced features. Just too much to explain.
Simple solution with unnest()
One unnest() can now take multiple arrays to unnest in parallel.
SELECT 'SELECT * FROM unnest(
''{sl_no, username, designation, salary}''::text[]
, ' || string_agg(quote_literal(ARRAY[sl_no::text, username::text, designation::text, salary::text])
|| '::text[]', E'
, ')
|| E')
AS t(col,' || string_agg('row' || sl_no, ',') || ')' AS sql
FROM tbl;
Result:
SELECT * FROM unnest(
'{sl_no, username, designation, salary}'::text[]
,'{10,Joe,Music,1234}'::text[]
,'{11,Bob,Movie,2345}'::text[]
,'{12,Dave,Theatre,2356}'::text[])
AS t(col,row1,row2,row3,row4)
SQL Fiddle running on pg 9.3.
这篇关于PostgreSQL 将列转换为行?转置?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！