Postgresql Row to Columns

Transpose latest rows per user to columns

Use crosstab() from the tablefunc module.

SELECT * FROM crosstab(
   $$SELECT user_id, user_name, rn, email_address
     FROM  (
        SELECT u.user_id, u.user_name, e.email_address
             , row_number() OVER (PARTITION BY u.user_id
                            ORDER BY e.creation_date DESC NULLS LAST) AS rn
        FROM   usr u
        LEFT   JOIN email_tbl e USING (user_id)
        ) sub
     WHERE  rn < 4
     ORDER  BY user_id
   $$
  , 'VALUES (1),(2),(3)'
   ) AS t (user_id int, user_name text, email1 text, email2 text, email3 text);

I used dollar-quoting for the first parameter, which has no special meaning. It's just convenient to escape single quotes in the query string, which is a common case:

Insert text with single quotes in PostgreSQL

Detailed explanation and instructions:

PostgreSQL Crosstab Query

And in particular, for "extra columns":

Pivot on Multiple Columns using Tablefunc

The special difficulties here are:

The lack of key names.

→ We substitute with row_number() in a subquery.
The varying number of emails.

→ We limit to a max. of three in the outer SELECT and use crosstab() with two parameters, providing a list of possible keys.

Pay attention to NULLS LAST in the ORDER BY.

Postgres Transpose Rows to Columns Based on Column Value

Assuming you have a fixed 4 quarters per year which you want to display, use pivoting logic:

SELECT
    stock,
    year,
    MAX(amount) FILTER (WHERE statement = 'Q1 Earnings') AS "Q1 Earnings",
    MAX(amount) FILTER (WHERE statement = 'Q2 Earnings') AS "Q2 Earnings",
    MAX(amount) FILTER (WHERE statement = 'Q3 Earnings') AS "Q3 Earnings",
    MAX(amount) FILTER (WHERE statement = 'Q4 Earnings') AS "Q4 Earnings"
FROM statement
GROUP BY
    stock,
    year;

PostgreSQL transpose rows in to columns

you can try this:

  select sales_report.date, 
  sum(case when id=1 then quantity else 0 end) as '1',
  sum(case when id=2 then quantity else 0 end) as '2',
  sum(case when id=3 then quantity else 0 end) as '3',
  sum(case when id=4 then quantity else 0 end) as '4',
  sum(case when id=5 then quantity else 0 end) as '5',
  sum(case when id=6 then quantity else 0 end) as '6',
  sum(case when id=7 then quantity else 0 end) as '7',
  sum(case when id=8 then quantity else 0 end) as '8',
  sum(case when id=9 then quantity else 0 end) as '9',
  sum(case when id=10 then quantity else 0 end) as '10',
  sum(case when id=11 then quantity else 0 end) as '11',
  sum(case when id=12 then quantity else 0 end) as '12',
  sum(case when id=13 then quantity else 0 end) as '13'
  from sales_report where date = '2018-10-04' GROUP BY Date ORDER BY date ASC

Transform Postgres rows data into columns based on condition

Below is the query to create the respective table and insert some data.

begin;
create table trans_custom_fields(id text, _value text,transid integer );
insert into trans_custom_fields values('ACCOUNT_HOLDER_NAME','Manoj Sharma',1);
insert into trans_custom_fields values('ACCOUNT_NUMBER',    '113565TTE44656',   1);
insert into trans_custom_fields values(    'RECIPT_NUMBER', '24324.',   1);
insert into trans_custom_fields values(   'ACCOUNT_HOLDER_NAME',    'Another User', 2);
insert into trans_custom_fields values('ACCOUNT_NUMBER',    '35546656TRFG23',   2);
insert into trans_custom_fields values('RECIPT_NUMBER', '24324686', 2);
commit;

Now I want to do the transformation for this data and here I am going to use crosstab feature of Postgres.

SELECT *
FROM   crosstab(
   'SELECT transid, id, _value
    FROM   trans_custom_fields
    ORDER  BY 1,2'
   ) AS ct (transid int, ACCOUNT_HOLDER_NAME text, ACCOUNT_NUMBER text);

I am really thankful to crosstab example for just helping me understand and write my own answer for my question, also thank @mark who does provide the queries and resolution but that fit better as of now.

How to loop through columns within a row

No need for PL/pgSQL or a loop. You can convert the row from t1 to a JSON value, then turn those key/value pairs into rows:

insert into t2 (column_name, value)
select x.col, to_jsonb(x.val)
from t1 
   cross join jsonb_each_text(to_jsonb(t1)) as x(col, val)
where t1.id = 42;