Crosstab transpose query request

Question

Using Postgres 9.3.4, I've got this table:

create table tbl1(country_code text, metric1 int, metric2 int, metric3 int);
insert into tbl1 values('us', 10, 20, 30);
insert into tbl1 values('uk', 11, 21, 31);
insert into tbl1 values('fr', 12, 22, 32);

I need a crosstab query to convert it to this:

create table tbl1(metric text, us int, uk int, fr int);
insert into tbl1 values('metric1', 10, 11, 12);
insert into tbl1 values('metric2', 20, 21, 22);
insert into tbl1 values('metric3', 30, 31, 32);

As an added bonus, I'd love a rollup:

create table tbl1(metric text, total int, us int, uk int, fr int);
insert into tbl1 values('metric1', 33, 10, 11, 12);
insert into tbl1 values('metric2', 63, 20, 21, 22);
insert into tbl1 values('metric3', 93, 30, 31, 32);

I'm done staring at the crosstab spec, I have it written with case statements but it's mad unruly and long, so can someone who's fluent in crosstab please whip up a quick query so I can move on?

DO you have more countries? or just these three? – Jorge Campos Oct 12 '15 at 03:56 — Jorge Campos, Oct 12 '15 at 03:56
http://stackoverflow.com/a/14087244/460557 – Jorge Campos Oct 12 '15 at 03:57 — Jorge Campos, Oct 12 '15 at 03:57

Erwin Brandstetter · Accepted Answer · 2022-03-27T23:05:03.580

The special difficulty is that your data is not ready for cross tabulation. You need data in the form row_name, category, value. You can get that with a UNION query:

SELECT 'metric1' AS metric, country_code, metric1 FROM tbl1
UNION ALL
SELECT 'metric2' AS metric, country_code, metric2 FROM tbl1
UNION ALL
SELECT 'metric3' AS metric, country_code, metric3 FROM tbl1
ORDER  BY 1, 2 DESC;

But a smart LATERAL query only needs a single table scan and will be faster:

SELECT x.metric, t.country_code, x.val
FROM   tbl1 t
     , LATERAL (VALUES
         ('metric1', metric1)
       , ('metric2', metric2)
       , ('metric3', metric3)
       ) x(metric, val)
ORDER  BY 1, 2 DESC;

SELECT * FROM crosstab(
   $$
   SELECT x.metric, t.country_code, x.val
   FROM   tbl1 t
   , LATERAL (
      VALUES
        ('metric1', metric1)
      , ('metric2', metric2)
      , ('metric3', metric3)
      ) x(metric, val)
   ORDER  BY 1, 2 DESC
   $$
   ) AS ct (metric text, us int, uk int, fr int);

List country names in alphabetically descending order (like in your demo). This also assumes all metrics are defined NOT NULL.

If one or both are not the case, use the 2-parameter form instead:

PostgreSQL Crosstab Query

Add "rollup"

I.e. totals per metric:

SELECT * FROM crosstab(
   $$
   SELECT x.metric, t.country_code, x.val
   FROM  (
      TABLE tbl1
      UNION ALL
      SELECT 'zzz_total', sum(metric1)::int, sum(metric2)::int, sum(metric3)::int  -- etc.
      FROM tbl1
      ) t
   , LATERAL (
      VALUES
        ('metric1', metric1)
      , ('metric2', metric2)
      , ('metric3', metric3)
      ) x(metric, val)
   ORDER  BY 1, 2 DESC
   $$
   ) AS ct (metric text, total int, us int, uk int, fr int);

'zzz_total' is an arbitrary label, that must sort last alphabetically (or you need the 2-parameter form of crosstab()).

If you have lots of metrics columns, you might want to build the query string dynamically. Related:

Also note that the upcoming Postgres 9.5 (currently beta) introduces a dedicated SQL clause for ROLLUP.
Related:

Spatial query on large table with multiple self joins performing slow

Thanks for clearing that up for me and giving a very concise solution. How would I add the rollup value to that? Would I need to pre-calculate that by adding a country_code called total to the source data before doing the crosstab? — user433342, Oct 12 '15 at 05:27

Crosstab transpose query request

1 Answers1

Add "rollup"

Linked