0

I need to realize a pivot table in Postgres, but I have no idea how to do it. The function to use should be the crosstab(), but I'm not sure I know how to use it properly.

Given a table like this:

column a | column b | column c | value 1 | value 2

       0 |
       1 |
       2 |
       3 |
       4 |

I have to get something like this:

column c | Column b | 0     | 1     | 2     | 3     | 4 

         |          |value 1|value 1|value 1|value 1|value 1|
         |          |

So I need to get the row 0, ... , 4 as columns, and value 1 as values. crosstab() function wants 3 columns, nut I need column c and column b both to appear.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
stefano542
  • 81
  • 1
  • 1
  • 2

1 Answers1

1

"column c" and "column b" are a so-called "extra" columns. Put them after the row name and before the category:

SELECT * FROM crosstab(
      'SELECT "column a", "column c", "column b"
              ,"value 2", "value 1"
       FROM   tbl
       ORDER  BY 1'
     , 'VALUES (0), (1), (2), (3), (4)'
   ) AS ct (
    "column a" text
  , "column c" text
  , "column b" text
  , "0" int
  , "1" int
  , "2" int
  , "3" int
  , "4" int);

To address your comment:
These are nested numeric literals, which need no quoting:

     , 'VALUES (0), (1), (2), (3), (4)'

String literals require quoting (I use to dollar-quoting for the outer quote now):

     , $$VALUES ('V0'), ('V1'), ('V2'), ('V3'), ('V4')$$

Or:

     , $$SELECT unnest('{V0,V1,V2,V3,V4}'::text[])$$

db<>fiddle here

See:

About crosstab():

... with extra columns:

And consider sane column names. (Assuming given names are just symbolic.)

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • hank you! unluckly, using this code : SELECT * FROM crosstab( 'SELECT "Animals"."name", "Animals"."age", "Animals"."race","Animals".value FROM "Animals" ORDER BY 1' ,'VALUES (V0), (V1), (V2), (V3), (V4)') AS ct ( "Name" text , "Age" text , "V0" DOUBLE PRECISION , "V1" DOUBLE PRECISION , "V2" DOUBLE PRECISION , "V3" DOUBLE PRECISION , "V4" DOUBLE PRECISION); is giving me the following error: ERROR: column "v0" does not exist – stefano542 Sep 29 '19 at 14:48
  • @stefano542: Missing quotes around your string literals. I added some more explanation. – Erwin Brandstetter Oct 07 '19 at 21:21