Rearrange PSQL table

Question

I have a table that looks like this

------------------------
 Letter | Status | Count
 A      | active | 3
 A      | offline| 1
 B      | active | 12
 B      | offline| 3
 C      | fixing | 4
 C      | active | 2

How can I make it looks like this

-----------------------------------
 Letter | active | offline | fixing
 A      | 3      | 1       | 0
 B      | 12     | 3       | 0
 C      | 2      | 0       | 4

Dynamic Pivot several examples on Site already! Here's [One example](http://stackoverflow.com/questions/28337765/postgresql-9-3-dynamic-pivot-table) and a [second](http://stackoverflow.com/questions/28087948/dynamic-pivot-query-using-postgresql-9-3) though it's unclear if you want a query or if you really want to change the table structure. — xQbert, Feb 26 '16 at 21:39
@xQbert both examples are not truly "dynamic" because them requires hardcoded resultset structure. BTW. — Abelisto, Feb 26 '16 at 22:11
Perhaps this is a better example then: http://stackoverflow.com/questions/15506199/dynamic-alternative-to-pivot-with-case-and-group-by/15514334#15514334 — xQbert, Feb 26 '16 at 22:17

score 0 · Accepted Answer · answered Feb 26 '16 at 22:56

Use crosstab() for this case:

-- DDL and data
CREATE TABLE old (
  letter TEXT,
  status TEXT,
  status_count INTEGER
);
INSERT INTO old VALUES
  ('A','active',3),
  ('A','offline',1),
  ('B','active',12),
  ('B','offline',3),
  ('C','fixing',4),
  ('C','active',2);
-- add crosstab function
CREATE EXTENSION tablefunc;

CREATE TABLE new_table AS (
  SELECT * FROM crosstab(
  'SELECT * FROM old ORDER BY 1,2',
  'SELECT DISTINCT status FROM old ORDER BY 1'
  ) AS new_table(letter TEXT,active INTEGER,fixing INTEGER,offline INTEGER)
);
-- validate
SELECT * FROM new_table;

Result:

 letter | active | fixing | offline 
--------+--------+--------+---------
 A      |      3 |        |       1
 B      |     12 |        |       3
 C      |      2 |      4 |        
(3 rows)

Rearrange PSQL table

1 Answers1