Crosstab Transpose Query Request

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

Solution 1:

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
UNIONALLSELECT'metric2'AS metric, country_code, metric2 FROM tbl1
UNIONALLSELECT'metric3'AS metric, country_code, metric3 FROM tbl1
ORDERBY1, 2DESC;

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)
ORDERBY1, 2DESC;

Related:

• What is the difference between LATERAL and a subquery in PostgreSQL?
• SELECT DISTINCT on multiple columns

Using the simple form of crosstab() with 1 parameter with this query as input:

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)
   ORDERBY1, 2DESC$$
   )
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
      UNIONALLSELECT'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)
   ORDERBY1, 2DESC$$
   )
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:

• How to perform the same aggregation on every column, without listing the columns?
• Executing queries dynamically in PL/pgSQL

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