如何在PostgreSQL中进行透视 [英] How to do Pivoting in PostgreSQL

问题描述

我是PostgreSQL的新手.

I am new to PostgreSQL.

假设我有一张桌子，

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

如果我在SQL Server中执行枢轴操作

If I do a Pivot in SQL Server as

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;

我得到的输出为

colorname   hexa    r   g   b
Blue    #0000FF 0   0   255
Indigo  #4B0082 75  0   130
Violet  #8B00FF 139 0   255

如何使用PostgreSQL做同样的事情?

How to do the same using PostgreSQL?

我的尝试是

SELECT *
FROM crosstab
(
    'SELECT 
        colorname
        ,hexa
        ,rgb
        ,rgbvalue
    FROM tblPivot'
)AS ct(colorname text, hexa text, rgb text, rgbvalue int);

但是出现错误:

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**

在PostgreSQL中是否有任何优雅的方法(任何内置函数...)，这样做的标准做法是什么?

Is there any elegant way of doing so in PostgreSQL (any built in function...) What is the standard practice of doing so ?

推荐答案

这可以表示为JOIN:

This can be expressed as a 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'
;

这篇关于如何在PostgreSQL中进行透视的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！