How to do Pivoting in PostgreSQL

Question

I am new to 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

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?

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 ?

Laxmikant Dange · Answer 1 · 2017-02-20T13:17:29.487

56

Run this

CREATE EXTENSION tablefunc;

and try to execute your query

edited Feb 20 '17 at 13:17

answered Aug 21 '13 at 07:58

Laxmikant Dange

7,606
6
40
65

3

if I run this I get `ERROR: extension "tablefunc" already exists` but when I try to use it I get `ERROR: function crosstab(unknown) does not exist`. I'm using Postgres 9.2.1 on Mac 10.9.2. Any ideas? – Black Apr 15 '14 at 01:09
3

Make sure you are connected to your database with \c db_name and then run the command above. – sm0ke21 May 15 '14 at 10:13
1

This [answer](https://stackoverflow.com/a/23073241/177116) has answer to the question raised in the @Black's comment. – Andrew-Dufresne Sep 16 '17 at 18:05

SingleNegationElimination · Accepted Answer · 2011-09-29T14:21:01.803

8

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'
;

edited Sep 29 '11 at 14:21

answered Sep 27 '11 at 03:22

SingleNegationElimination

151,563
33
264
304

But why crosstab didnot work... does it not support in the current version that I am using? – priyanka.sarkar Sep 27 '11 at 03:29
10

`crosstab()` belongs to the `tablefunc` module. You will have to enable it using `CREATE EXTENSION`. That said; the convenience it offers may not be that great; postgresql is fantastically good at optimizing joins, and this sort of code will be more recognizable across more databases and to more developers. – SingleNegationElimination Sep 27 '11 at 03:42
If you do a LOT of crosstab stuff, the tablefunc contrib module can be great. If you're on Ubuntu, you can add tablefunc functions to a db by doing this: psql dbname -f /usr/share/postgresql/8.4/contrib/tablefunc.sql or something similar. – Scott Marlowe Sep 27 '11 at 11:30
I "think" first line should be "SELECT c.colorname, c.hexa, r.rgbvalue, g.rgbvalue, b.rgbvalue" instead, but otherwise +1 for this answer. – plang Sep 29 '11 at 13:26

How to do Pivoting in PostgreSQL

2 Answers2

Linked