Transpose rows to columns in PostgreSQL?

Question

Does any one know how to transpose rows to columns in PostgreSQL? For example I have the following table:-

  Period                T1            T2           Difference
----------           -------       --------        -----------
 MAR-2013             34525           319            34206
 AUG-2014             35632         14453            21179
 OCT-2014             28124         10082            18042
 JUN-2014             20571          9353            11218
 MAY-2015             44963         39097             5866
 FEB-2013              1941           127             1814
 JUL-2014             14510         12965             1545
 APR-2015             32446         30992             1454
 MAY-2014             13510         12136             1374
 APR-2014              8042          6967             1075
 JAN-2013              1107            86             1021
 DEC-2014             30764         30076              688
 SEP-2014              6886          6380              506
 MAR-2014              4695          4242              453

---

But I need output as

Period  MAR-2013  AUG-2014 OCT-2014 JUN-2014 MAY-2015 FEB-2013 JUL-2014 APR-2015 MAY-2014  APR-2014  JAN-2013  DEC-2014  SEP-2014  MAR-2014 
------- --------  -------- -------  -------  -------  -------  -------  -------  -------   -------   -------   -------   -------   -------
  T1    34525     35632    28124     20571   44963  1941    14510     32446    13510      8042      1107      30764     6886      4695  
  T2      319     14453    10082      9353   39097       127    12965     30992    12136      6967        86      30076     6380      4242 
Diff    34206     21179    18042     11218    5866      1814     1545      1454     1374      1075      1021        688      506       453

Answer 1

I wrote a simple query for example. The process consists of 2 sections. In SECTION 1 each time I drop the "table" and recreate it. This table using only to view JSON data as records.

SECTION 1:

do 
$body$
declare
    per RECORD;
    v_sql text;
    v_del text;
begin 
    v_del = '';

    v_sql = 'DROP TABLE period_names; CREATE TABLE period_names (';
    FOR per IN
       SELECT distinct "period" from table1
    LOOP    
        v_sql = v_sql || v_del || '  "' || per."period"::text || '" varchar(100) NOT NULL';
        v_del = ', ';
    end loop;
    v_sql := v_sql || ');';
    execute v_sql;
   
end; 
$body$
LANGUAGE 'plpgsql';

In SECTION 2 I am using the real table to calculate sum values by period, then using JSON type for showing rows as columns (pivot table)

SECTION 2:

select pname as p_type, jsonb_populate_record(null::period_names, tt1.jsondata) as periods
from (
    select 'T1' as pname, jsonb_object_agg(tt."period", tt.t1) as jsondata from table1 as tt
    union all 
    select 'T2' as pname, jsonb_object_agg(tt."period", tt.t2) as jsondata from table1 as tt
    union all 
    select 'Dif' as pname, jsonb_object_agg(tt."period", tt.difference) as jsondata from table1 as tt
) tt1;