Query - Date as a column header

Question

I had this query that returns a result like this:

Query

select 
   d.veiculo, d.data_op, d.total_custo_op
from 
   fato_distribuicao d
left join dim_frota f 
   on d.veiculo = f.placa
left join schema_staging.staging_rotas_percorridas p 
   on p.placa = d.veiculo and p.data_operacao = d.data_op
where 
   d.uneg_dist = 'RJA'
   and d.data_op between '2016-07-18' and '2016-07-23'
   and f.tipo1 = 'AGREGADO'
group by d.veiculo, d.data_op, d.total_custo_op, p.setor
order by d.veiculo, d.data_op;

Result

veiculo data_op     total_custo_op

BTB7632 2016-07-19  219
BTB7632 2016-07-21  150
BTB7632 2016-07-22  176
DMI1082 2016-07-18  150
DMI1082 2016-07-19  168
DMI1082 2016-07-20  136
DMI1082 2016-07-21  163
DMI1082 2016-07-22  184
EJC1713 2016-07-18  205
EJC1713 2016-07-19  185
EJC1713 2016-07-20  190
EJC1713 2016-07-21  200
EJC1713 2016-07-22  179
GZG1647 2016-07-18  248
GZG1647 2016-07-20  279
GZG1647 2016-07-21  276
GZG1647 2016-07-22  314
GZG1647 2016-07-23  188

But I would need to transform this result and present this query with the range of dates as a column header and total_custo_op as data, like this:

Expected

veiculo 2016-07-18  2016-07-19  2016-07-20  2016-07-21  2016-07-22  2016-07-23
BTB7632 null        219         null        150         176         null
DMI1082 150         168         136         163         184         null
EJC1713 205         185         190         200         179         null
GZG1647 248         null        279         276         314         188

I did a pivot query with ARRAY_AGG but I just can separate the dates as data. And I can't use the tablefunc module.

Google "PIVOT Postgres" ... are the number of date columns fixed? — Tim Biegeleisen, Sep 02 '16 at 12:47
@TimBiegeleisen the numbers of date can be fixed, it usually will be 5 or 6 (work days), but I can fix to 7 days. I googled this but I didn't have success. — thiagofred, Sep 02 '16 at 13:18
You should look at the docs for the [tablefunc contrib module](https://www.postgresql.org/docs/current/static/tablefunc.html). It includes functions like `crosstab`, which should be able to do what you want. — jmelesky, Sep 02 '16 at 17:12
@jmelesky I know it's possible with tablefunc, but it's the contrib module isn't installed in my db, and I don't have permission to intall... So I'd like another way to do this, if it's possible... — thiagofred, Sep 02 '16 at 17:47
You could write your own functions to do a similar thing to `crosstab`. If you can't make custom functions, then I don't think this is possible within the database. — jmelesky, Sep 02 '16 at 19:06

score 1 · Accepted Answer · edited May 23 '17 at 12:22

Fix base query

First off, your query has problems. You have a LEFT JOIN, followed by a WHERE condition, that's nonsense.

Move the condition f.tipo1 = 'AGREGADO' to the join clause if you actually want a LEFT JOIN. But that wouldn't make sense in your query. Rather, use a plain JOIN.

And it's nonsense to add p.setor to GROUP BY while you don't show it in the result. Actually, GROUP BY does not make any sense in your query at all, since you do not aggregate anything. You may be looking for DISTINCT or DISTINCT ON?

Select first row in each GROUP BY group?

So:

SELECT d.veiculo, d.data_op, d.total_custo_op
FROM   fato_distribuicao d
JOIN   dim_frota         f ON f.placa = d.veiculo
LEFT   JOIN schema_staging.staging_rotas_percorridas p ON p.placa = d.veiculo
                                                      AND p.data_operacao = d.data_op
WHERE  d.uneg_dist = 'RJA'
AND    d.data_op BETWEEN '2016-07-18' AND '2016-07-23'
AND    f.tipo1 = 'AGREGADO'
-- GROUP  BY d.veiculo, d.data_op, d.total_custo_op  -- , p.setor  -- ??
ORDER  BY d.veiculo, d.data_op;

Pivot solution

To pivot without crosstab(), you can use aggregates on CASE statements like this:

SELECT veiculo
     , min(CASE WHEN data_op = '2016-07-18' THEN total_custo_op END) AS "2016-07-18"
     , min(CASE WHEN data_op = '2016-07-19' THEN total_custo_op END) AS "2016-07-19"
     , -- etc.
FROM  (
  <query from above>
   ) sub
GROUP  BY 1;

crosstab() would be faster and more elegant, though:

PostgreSQL Crosstab Query

Thanks man. So, I add `p.setor` to `GROUP BY` was really wrong, because I shortened the query but forgot this part. The condition `f.tipo1 = 'AGREGADO'` I moved in the `JOIN` clause. I didn't try to use this clause. But I don't know how to fix the `LEFT JOIN` followed by a `WHERE` clause, because it's necessary to use the both clause. Anyway thank you so much to help me. — thiagofred, Sep 05 '16 at 12:45

Query - Date as a column header

1 Answers1

Fix base query

Pivot solution