PostgreSQL: Pivot a 2-column result set into a single-row table

Question

Struggling with what I thought would be a straightforward operation...

EDIT: SQLFiddle available here: http://sqlfiddle.com/#!15/11711/1/0

Using PostgreSQL 9.4, pretend I have a query that returns this two-column set:

CATEGORY | TOTAL
all      | 14
soccer   | 5
baseball | 6
hockey   | 3

However I'd prefer to pivot it into a single-row set:

ALL | SOCCER | BASEBALL | HOCKEY
14  | 5      | 6        | 3

In other words, I want all my "CATEGORY" values to become columns, with the corresponding "TOTAL" value to be placed in the first row under the appropriate column.

I've been trying to use CROSSTAB()... but as of now I'm getting the following error:

ERROR: a column definition list is required for functions returning "record"

For reference, here's what I'm trying to put as my SQL command:

SELECT * FROM crosstab(
$$
    WITH "countTotal" AS (
        SELECT text 'all' AS "sportType", COUNT(*) AS "total"
        FROM log
        WHERE type = 'SPORT_EVENT_CREATED'
        GROUP BY "sportType"
    ),
    "countBySportType" AS (
        SELECT sport_type AS "sportType", COUNT(*) AS "total"
        FROM log
        WHERE type = 'SPORT_EVENT_CREATED'
        GROUP BY "sportType"
    )
    SELECT * FROM "countTotal" 
    UNION 
    SELECT * FROM "countBySportType"
$$
)

can you create a sqlfiddle for the tables? – Nandakumar V May 28 '15 at 09:19 — Nandakumar V, May 28 '15 at 09:19
@NandakumarV SQLFiddle link added! – hbCyber May 28 '15 at 09:55 — hbCyber, May 28 '15 at 09:55

score 1 · Answer 1 · edited May 23 '17 at 11:48

I think you have to specify names and types of the output columns. From the postgres manual tablefunc

The crosstab function is declared to return setof record, so the actual names and types of the output columns must be defined in the FROM clause of the calling SELECT statement, for example:
SELECT * FROM crosstab('...') AS ct(row_name text, category_1 text, category_2 text);

You have to use crosstabN(text) to use it with dynamic number of columns. This PostgreSQL Crosstab Query whole lot of details about the cross tab query.

One more post Dynamic alternative to pivot with CASE and GROUP BY

PostgreSQL: Pivot a 2-column result set into a single-row table

1 Answers1

Linked