blocks|key|198492|text|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"|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|198493|参考：|unstyled|198494|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>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"
</code></pre>

Reference : <a href="https://stackoverflow.com/questions/10624864/transpose-column-headers-to-rows-in-postgresql/10625294#10625294">convertingColumnsToRows</a>

blocks|key|4699899|text|在纯SQL或PL/pgSQL中没有合适的方法来实现这一点。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4699900|在从数据库获取数据的应用程序中这样做会更好。|4699901|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

There is no proper way to do this in plain SQL or PL/pgSQL.

It will be way better to do this in the application, that gets the data from the DB.

blocks|key|198103|text|如果(像我一样)您需要来自bash脚本的此信息，请注意，psql有一个简单的命令行开关来告诉它将表列输出为行：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|198104|psql+mydbname+-x+-A+-F=+-c+"SELECT+*+FROM+foo+WHERE+id=123"|code-block|syntax|javascript|198105|-x选项是让psql将列输出为行的关键。|offset|length|style|CODE|198106|entityMap^0|0|0|0|2|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

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:

<pre><code>psql mydbname -x -A -F= -c "SELECT * FROM foo WHERE id=123"
</code></pre>

The <code>-x</code> option is the key to getting psql to output columns as rows.

blocks|key|198506|text|我有一种比Erwin上面提到的更简单的方法，那就是Postgres+(我认为它应该适用于所有支持SQL标准的主要关系数据库)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|198507|您可以简单地使用UNION而不是交叉表：|198508|SELECT+text+'a'+AS+"text"+UNION+SELECT+'b';

+text
------
+a
+b
(2+rows)|code-block|syntax|javascript|198509|当然，这取决于您要应用这一点的情况。考虑到您事先知道需要哪些字段，甚至在查询不同的表时也可以采用这种方法。即：|198510|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+++++++++++++%7C+Total
------------------%7C--------
+My+first+metric++%7C+++++10
+My+second+metric+%7C+++++20
(2+rows)|198511|这是一种更易维护的方法，IMHO。有关更多信息，请查看此页面：https://www.postgresql.org/docs/current/typeconv-union-case.html|offset|length|198512|entityMap|0|LINK|mutability|MUTABLE|url|https://www.postgresql.org/docs/current/typeconv-union-case.html^0|0|0|0|0|0|V|1S|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Z|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|10|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|11|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|12|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|13|8|@]|9|@$O|14|P|15|1|16]]|A|$]]|$1|Q|3|-4|5|6|7|17|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|X]]]]

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)

You can use simply UNION instead of crosstab:

<pre><code>SELECT text 'a' AS "text" UNION SELECT 'b';

 text
------
 a
 b
(2 rows)
</code></pre>

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.:

<pre><code>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)
</code></pre>

It's a more maintainable approach, IMHO. Look at this page for more information: <a href="https://www.postgresql.org/docs/current/typeconv-union-case.html" rel="noreferrer">https://www.postgresql.org/docs/current/typeconv-union-case.html</a>

I have a PostgreSQL function (or table) which gives me the following output:

<pre><code>Sl.no username Designation salary etc..
 1 A XYZ 10000 ...
 2 B RTS 50000 ...
 3 C QWE 20000 ...
 4 D HGD 34343 ...
</code></pre>

Now I want the Output as below:

<pre><code>Sl.no 1 2 3 4 ...
 Username A B C D ...
 Designation XYZ RTS QWE HGD ...
 Salary 10000 50000 20000 34343 ...
</code></pre>

How to do this?

PostgreSQL convert columns to rows? Transpose?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个PostgreSQL函数(或表)，它给出了以下输出：Sl.no    username    Designation    salary   etc.. 1        A           XYZ            10000    ... 2        B           RTS      ...

问PostgreSQL是否将列转换为行？转置？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问PostgreSQL是否将列转换为行？转置？EN