Use Row Values as Columns in Postgresql

Use row values as columns in PostgreSQL

A crosstab() query for your example would look like this:

To fill in 0 for resulting NULL values (request in comment), use COALESCE():

SELECT brand_id
     , COALESCE(jan, 0) AS "Jan-2012"
     , COALESCE(feb, 0) AS "Feb-2012"
     , COALESCE(mar, 0) AS "Mar-2012"
     , COALESCE(apr, 0) AS "Apr-2012"
FROM crosstab(
       'SELECT brand_id, month, total
        FROM   brands
        ORDER  BY 1'

       ,$$VALUES ('Jan-2012'::text), ('Feb-2012'), ('Mar-2012'), ('Apr-2012')$$
 ) AS ct (
   brand_id int
 , jan numeric    -- use actual data type!
 , feb numeric
 , mar numeric
 , apr numeric);

Detailed explanation and links in this related answer:

PostgreSQL Crosstab Query

Aside: not using the reserved word "date" as column name and you shouldn't either, even if Postgres allows it.

How to use row values as columns in a SELECT statement in PostgreSQL?

As Adam Silenko said, you cannot add a column at runtime. The best thing that you can do is to use 2 queries. One to a function that will create a temporary table with the columns you need and another one to query the table and get the results. This is explained here.

The function that will create the temporary table:

CREATE OR REPLACE FUNCTION get_order(orderId BIGINT)
  RETURNS VOID AS
  $$
  DECLARE column_names varchar[];
  DECLARE column_values float[];
  DECLARE final_select TEXT := 'SELECT id, name points_columns FROM orders where id=' || orderId;
  DECLARE create_table_statement TEXT := 'CREATE TEMP TABLE temp_result_table ON COMMIT DROP AS select_statement';
  DECLARE columns_values_concatenated TEXT := '';
  BEGIN
    SELECT array_agg(abbrev),  array_agg(CASE WHEN amount IS NULL THEN 0 ELSE amount END)
    into column_names, column_values FROM
     (SELECT abbrev, order_points.amount as amount FROM points LEFT JOIN order_points ON points.id = order_points.points_id and order_id = orderId
     ORDER BY points.id) points_amount;

     FOR i IN 1 .. array_upper(column_names, 1)
       LOOP
       columns_values_concatenated := columns_values_concatenated || ', ' || column_values[i] || ' as ' || column_names[i];
     end loop;
    final_select := replace(final_select, 'points_columns',columns_values_concatenated);
    create_table_statement:= replace(create_table_statement, 'select_statement', final_select);
    EXECUTE create_table_statement;
end;

$$ LANGUAGE Plpgsql;

We use 2 arrays, column_names and column_values to store the names ("sn", "mw", "cf) and values for those names for the selected order respectively.

We use those 2 arrays to generate the select statement (in the current code I am getting only the id and name from the orders table but you can easily change that). We store the select statement into the final_select variable. Finally we add the generated select statement to the create_table_statement and we create and fill the temporary table.

Now, as explained in the link above, because we need 2 queries to access the data, we have to execute both of the queries in a single transaction (in order to avoid name collisions if we are calling the function multiple times).

BEGIN;
SELECT * FROM get_order(1);
SELECT * FROM temp_result_table;
COMMIT; --The temporary table will be dropped on commit

Query issue (row values to columns) in PostgreSQL

CASE statements are the poor man's surrogate for a proper crosstab() function:

SELECT a.id
      ,max(CASE WHEN myorder = 1 THEN result END) AS result_1
      ,max(CASE WHEN myorder = 2 THEN result END) AS result_2
      ,max(CASE WHEN myorder = 3 THEN result END) AS result_3
FROM   mytab
GROUP  BY id
ORDER  BY id;

Only requires a single table scan and is therefore much faster than multiple joins.

BTW, never use reserved words like order as identifiers.

Details for this as well as a proper crosstab() query under this related question:

PostgreSQL Crosstab Query

Convert rows to columns by same column value in Postgres

Use conditional aggregation which in Postgres uses the filter syntax:

select id,
       max(value) filter (where name = 'x') as x,
       max(value) filter (where name = 'y') as y,
       max(value) filter (where name = 'z') as z
from t
group by id;

Postgres Convert Rows to Columns based on a Common field Using Crosstab

I think you can do this by using SUM and CASE WHEN:

SELECT dist_name::text,service_name::text
,SUM(case when status = 'APPROVED' then count else 0 end) approved_count
,SUM(case when status = 'REJECTED' then count else 0 end) rejected_count
,SUM(case when status = 'PENDING' then count else 0 end) pending_count
,SUM(count) as total_count
FROM mpr_application_status
GROUP BY dist_name,service_name

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.

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;

How to convert row values of a column to columns - JDBCTemplate and PostgreSQL

Your current table is a typical denormalized key value store. You may generate the normalized output you want by aggregating by id and then using max CASE expressions:

SELECT
    id,
    MAX(CASE WHEN info = 'desc'  THEN value END) AS desc,
    MAX(CASE WHEN info = 'lname' THEN value END) AS lname,
    MAX(CASE WHEN info = 'fname' THEN value END) AS fname,
    MAX(CASE WHEN info = 'dob'   THEN value END) AS dob
FROM yourTable
GROUP BY
    id
ORDER BY
    id;

Note that I don't have any column for the date, as you did not give logic for which date value should be retained for each id.

As for the Spring part of your question, you would probably have to use a native query to execute the above.

---

Related Topics