How to pivot a single row summary in postgresql

Question

SQLFiddle here: http://sqlfiddle.com/#!15/e8e49/2/0

I want to get some summary data from my table into rows instead of columns..

select 
  sum(n),
  avg(n),
  max(n),
  min(n)
from Table1

this returns a single row, multi-column result.

How can I get it into a result that's something like this:

Label | Res
-------------
min   | 2
max   | 3
count | 30
... etc ...

http://www.postgresql.org/docs/9.4/static/tablefunc.html if you can create extension. that won't work on fiddle though, cos you need to create extension first — Vao Tsun, Jun 24 '15 at 08:19
I can use the crosstab function - just can't figure out how I'd use it for this single row result — Guy Bowden, Jun 24 '15 at 09:57
http://stackoverflow.com/questions/3002499/postgresql-crosstab-query — Vao Tsun, Jun 24 '15 at 09:59

Vao Tsun · Answer 1 · 2015-06-24T08:20:28.933

3

Postgres does not have a pivot trasformation...

    select 
      'sum' as "key",sum(n) as "value" from Table1
    union all
   select   'avg', avg(n) from Table1
    union all
      select   'max', max(n) from Table1
    union all
      select   'min', min(n) from Table1

or if you have superuser can try CREATE EXTENSION tablefunc

edited Jun 24 '15 at 08:20

answered Jun 24 '15 at 08:10

Vao Tsun

47,234
13
100
132

How to pivot a single row summary in postgresql

1 Answers1