Using crosstab, dynamically loading column names of resulting pivot table in one query?

Question

The gem we have installed (Blazer) on our site limits us to one query.

We are trying to write a query to show how many hours each employee has for the past 10 days. The first column would have employee names and the rest would have hours with the column header being each date. I'm having trouble figuring out how to make the column headers dynamic based on the day. The following is an example of what we have working without dynamic column headers and only using 3 days.

SELECT
    pivot_table.*
FROM
    crosstab(
    E'SELECT
        "User",
        "Date",
        "Hours"
    FROM
        (SELECT 
            "q"."qdb_users"."name" AS "User",
            to_char("qdb_works"."date", \'YYYY-MM-DD\') AS "Date",
            sum("qdb_works"."hours") AS "Hours"
        FROM 
            "q"."qdb_works" 
        LEFT OUTER JOIN
            "q"."qdb_users" ON 
            "q"."qdb_users"."id" = "q"."qdb_works"."qdb_user_id"
        WHERE
            "qdb_works"."date" > current_date - 20
        GROUP BY
            "User",
            "Date"
        ORDER BY
            "Date" DESC,
            "User" DESC) "x"
    ORDER BY 1, 2')
AS
    pivot_table (
        "User" VARCHAR,
        "2017-10-06" FLOAT,
        "2017-10-05" FLOAT,
        "2017-10-04" FLOAT
    );

This results in

| User | 2017-10-05 | 2017-10-04 | 2017-10-03 |
|------|------------|------------|------------|
| John | 1.5        | 3.25       | 2.25       |
| Jill | 6.25       | 6.25       | 6          |
| Bill | 2.75       | 3          | 4          |

This is correct, but tomorrow, the column headers will be off unless we update the query every day. I know we could pivot this table with date on the left and names on the top, but that will still need updating with each new employee – and we get new ones often.

We have tried using functions and queries in the "AS" section with no luck. For example:

AS
    pivot_table (
        "User" VARCHAR,
        current_date - 0 FLOAT,
        current_date - 1 FLOAT,
        current_date - 2 FLOAT
    );

Is there any way to pull this off with one query?

score 0 · Answer 1 · answered Oct 07 '17 at 10:42

You could select a row for each user, and then per column sum the hours for one day:

with    user_work as
        (
        select  u.name as user
        ,       to_char(w.date, 'YYYY-MM-DD') as dt_str
        ,       w.hours
        from    qdb_works w
        join    qdb_users u
        on      u.id = w.qdb_user_id
        where   w.date >= current_date - interval '2 days'
        )
select  User
,       sum(case when dt_str = to_char(current_date, 
                'YYYY-MM-DD') then hours end) as Today
,       sum(case when dt_str = to_char(current_date - 'interval 1 day',
                'YYYY-MM-DD') then hours end) as Yesterday
,       sum(case when dt_str = to_char(current_date - 'interval 2 days', 
                'YYYY-MM-DD') then hours end) as DayBeforeYesterday
from    user_work
group by
        user
,       dt_str

It's often easier to return a list and pivot it client side. That also allows you to generate column names with a date.

score 0 · Answer 2 · answered Oct 07 '17 at 11:39

Is there any way to pull this off with one query?

No, because a fixed SQL query cannot have any variability in its output columns. The SQL engine determines the number, types and names of every column of a query before executing it, without reading any data except in the catalog (for the structure of tables and other objects), execution being just the last of 5 stages.

A single-query dynamic pivot, if such a thing existed, couldn't be prepared, since a prepared query always have the same results structure, whereas by definition a dynamic pivot doesn't, as the rows that pivot into columns can change between executions. That would be at odds again with the Prepare-Bind-Execute model.

You may find some limited workarounds and additional explanations in other questions, for example: Execute a dynamic crosstab query, but since you mentioned specifically:

The gem we have installed (Blazer) on our site limits us to one query

I'm afraid you're out of luck. Whatever the workaround, it always need at best one step with a query to figure out the columns and generate a dynamic query from them, and a second step executing the query generated at the previous step.

Using crosstab, dynamically loading column names of resulting pivot table in one query?

2 Answers2