Change some of the rows to columns

Question

I have tried many ways to "transpose" rows by columns in Postgres but still I could not. Given a set of data in a Postgres view like this:

View1

ID|A_|B_|C_
--+--+--+--  
01|25|AA|1C  
02|50|BB|1C  
02|12|AA|2C  
03|27|BB|2C  
03|87|AA|3C  

I would like to be able make a query with this results:

ID| A_1| B_1| C_1| A_2| B_2| C_2  
--+----+----+----+----+----+----
01| 25 | AA | 1C | __ | __ | __
02| 50 | BB | 1C | 12 | AA | 2C
03| 27 | BB | 2C | 87 | AA | 3C

Is this even possible to try to doing in Postgres?. I'm trying to do something like this

select * from crosstab(
$$select id, a_, b_, c_, rn
  from (
     select v.id, v.a_, v.b_, v.c_, row_number() over (partition by v.id order by v.id desc nulls last) as rn
     from view1 v
     ) sub
  order by id
$$
, 'values (1), (2), (3)'
) as t (id varchar, a_1 bigint, b_1 varchar, c_1 varchar, a_2 varchar, b_2 varchar, c_2 varchar)

Answer 1

Basics for crosstab()

If you are unfamiliar with crosstab(), read this first:

• PostgreSQL Crosstab Query

Special difficulty

There are two complications here:

• Involved data types are inhomogeneous. (Makes me want to inspect your database design.) The common ground is to cast involved columns to text.

• You are mixing cross-tabulation with it's opposite (sometimes called "unpivot").

Building on a slightly simplified table:

CREATE TEMP TABLE view1(
   id int
 , a  int
 , b  text
 , c  text
);

I see two basic routes:

Route 1

First unpivot a, b, c into this form. Using the Postgres-specific unnest() in parallel. Explanation here:

• Is there something like a zip() function in PostgreSQL that combines two arrays?

SELECT id
     , unnest('{a,b,c}'::text[]) || rn          AS key
     , unnest(ARRAY[a::text, b::text, c::text]) AS val
FROM  (
    SELECT *, row_number() OVER (PARTITION BY id) AS rn  -- ORDER BY ??
    FROM   view1
    ) v;

Assuming there can only at most two rows for each id value. It's undefined how you determine "first" and "second". Result (using your test data):

id  key  val
1   a1   25
1   b1   AA
1   c1   1C
2   a1   50
2   b1   BB
2   c1   1C
2   a2   12
2   b2   AA
2   c2   2C
3   a1   27
3   b1   BB
3   c1   2C
3   a2   87
3   b2   AA
3   c2   3C

SQL Fiddle.
The rest is not possible on sqlfiddle.com, since the additional module tablefunc is not installed.

Feed this query to a standard crosstab() call. Plus, You might want to cast a1 and a2 back to bigint (or just int?) in the outer SELECT:

SELECT id, a1::bigint, b1, c1, a2::bigint, b2, c2  -- or just SELECT *
FROM crosstab(
   $$
   SELECT id
         ,unnest('{a,b,c}'::text[]) || rn
         ,unnest(ARRAY[a::text, b::text, c::text])
   FROM  (
      SELECT *, row_number() OVER (PARTITION BY id) AS rn
      FROM   view1
      ) v
   $$
 , $$SELECT unnest('{a1,b1,c1,a2,b2,c2}'::text[])$$
) AS t (id varchar, a1 text, b1 text, c1 text, a2 text, b2 text, c2 text);

Voilá.
For a more dynamic solution, combine these answers:

• How to unpivot a table in PostgreSQL
• Dynamic alternative to pivot with CASE and GROUP BY

Route 2

Run crosstab for a, b and c separately and join the derived tables on id.