如何在 PostgreSQL 中进行透视
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7563796/
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
How to do Pivoting in PostgreSQL
提问by priyanka.sarkar
I am new to PostgreSQL.
我是 PostgreSQL 的新手。
Suppose I have a table as under
假设我有一张桌子如下
colorname Hexa rgb rgbvalue
Violet #8B00FF r 139
Violet #8B00FF g 0
Violet #8B00FF b 255
Indigo #4B0082 r 75
Indigo #4B0082 g 0
Indigo #4B0082 b 130
Blue #0000FF r 0
Blue #0000FF g 0
Blue #0000FF b 255
If I do a Pivot in SQL Server as
如果我在 SQL Server 中做一个 Pivot 作为
SELECT colorname,hexa,[r], [g], [b]
FROM
(SELECT colorname,hexa,rgb,rgbvalue
FROM tblPivot) AS TableToBePivoted
PIVOT
(
sum(rgbvalue)
FOR rgb IN ([r], [g], [b])
) AS PivotedTable;
I get the output as
我得到的输出为
colorname hexa r g b
Blue #0000FF 0 0 255
Indigo #4B0082 75 0 130
Violet #8B00FF 139 0 255
How to do the same using PostgreSQL?
如何使用 PostgreSQL 做同样的事情？
My attempt is
我的尝试是
SELECT *
FROM crosstab
(
'SELECT
colorname
,hexa
,rgb
,rgbvalue
FROM tblPivot'
)AS ct(colorname text, hexa text, rgb text, rgbvalue int);
But geting error:
但得到错误：
ERROR: function crosstab(unknown) does not exist
LINE 2: FROM crosstab
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
********** Error **********
ERROR: function crosstab(unknown) does not exist**
Is there any elegant way of doing so in PostgreSQL (any built in function...) What is the standard practice of doing so ?
在 PostgreSQL 中是否有任何优雅的方法（任何内置函数......）这样做的标准做法是什么？
采纳答案by SingleNegationElimination
This can be expressed as a JOIN:
这可以表示为 JOIN：
SELECT c.colorname, c.hexa, r.rgbvalue, g.rgbvalue, b.rgbvalue
FROM (SELECT colorname, hexa
FROM sometable
GROUP BY colorname) c
JOIN sometable r ON c.colorname = r.colorname AND r.rgb = 'r'
JOIN sometable g ON c.colorname = g.colorname AND g.rgb = 'g'
JOIN sometable b ON c.colorname = b.colorname AND b.rgb = 'b'
;
回答by Laxmikant Dange
Run this
运行这个
CREATE EXTENSION tablefunc;
and try to execute your query
并尝试执行您的查询
