SQL PostgreSQL 将列转换为行？转置？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14084503/
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 convert columns to rows? Transpose?
提问by DonRaHulk
I have a PostgreSQL function (or table) which gives me the following output:
我有一个 PostgreSQL 函数（或表），它给了我以下输出：
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?
这该怎么做？
回答by Erwin Brandstetter
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:
通常，这受表可以容纳的最大列数限制。所以对于超过 1600 行（或更少）的表来说不是一个选项。细节：
Postgres 9.3 or older
Postgres 9.3 或更高版本
Dynamic solution with crosstab()
动态解决方案 crosstab()
- Completely dynamic, works for any table. Provide the table name in twoplaces:
- 完全动态，适用于任何表。在两个地方提供表名：
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()
简单的解决方案 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'\n , ') 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+
Postgres 9.4+
Dynamic solution with crosstab()
动态解决方案 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 attnuminstead of actual column names. Simpler and faster. Join the result to pg_attributeonce more or integrate column names like in the pg 9.3 example.
Generates a query of the form:
使用attnum而不是实际的列名进行操作。更简单更快。pg_attribute再次加入结果或像 pg 9.3 示例中那样整合列名。
生成以下形式的查询：
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()
简单的解决方案 unnest()
One unnest()can now take multiple arrays to unnest in parallel.
一个unnest()现在可以利用多个阵列到UNNEST并联。
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'\n, ')
|| E') \n 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 Fiddlerunning on pg 9.3.
SQL Fiddle在 pg 9.3 上运行。
回答by Mari
SELECT
unnest(array['Sl.no', 'username', 'Designation','salary']) AS "Columns",
unnest(array[Sl.no, username, value3Count,salary]) AS "Values"
FROM view_name
ORDER BY "Columns"
Reference : convertingColumnsToRows
回答by Stéphane
If (like me) you were needing this information from a bash script, note there is a simple command-line switch for psql to tell it to output table columns as rows:
如果（像我一样）您需要 bash 脚本中的这些信息，请注意 psql 有一个简单的命令行开关，可以告诉它以行的形式输出表列：
psql mydbname -x -A -F= -c "SELECT * FROM foo WHERE id=123"
The -xoption is the key to getting psql to output columns as rows.
该-x选项是让 psql 将列输出为行的关键。
回答by gbferreira
I have a simpler approach than Erwin pointed above, that worker for me with Postgres (and I think that it should work with all major relational databases whose support SQL standard)
我有一个比 Erwin 上面指出的更简单的方法，那个对我来说使用 Postgres 的工作人员（我认为它应该适用于所有支持 SQL 标准的主要关系数据库）
You can use simply UNION instead of crosstab:
您可以简单地使用 UNION 而不是交叉表：
SELECT text 'a' AS "text" UNION SELECT 'b';
text
------
a
b
(2 rows)
Of course that depends on the case in which you are going to apply this. Considering that you know beforehand what fields you need, you can take this approach even for querying different tables. I.e.:
当然，这取决于您要应用它的情况。考虑到您事先知道您需要哪些字段，您甚至可以采用这种方法来查询不同的表。IE：
SELECT 'My first metric' as name, count(*) as total from first_table UNION
SELECT 'My second metric' as name, count(*) as total from second_table
name | Total
------------------|--------
My first metric | 10
My second metric | 20
(2 rows)
It's a more maintainable approach, IMHO. Look at this page for more information: https://www.postgresql.org/docs/current/typeconv-union-case.html
恕我直言，这是一种更易于维护的方法。查看此页面了解更多信息：https: //www.postgresql.org/docs/current/typeconv-union-case.html
回答by Igor Romanchenko
There is no proper way to do this in plain SQL or PL/pgSQL.
在普通 SQL 或 PL/pgSQL 中没有正确的方法来做到这一点。
It will be way better to do this in the application, that gets the data from the DB.
在从数据库获取数据的应用程序中执行此操作会更好。
