CODEWITHSUNDEEP
sqlpostgresqlpivot
thiagofred
thiagofred

Reputation: 207

Query - Date as a column header

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.

Upvotes: 0

Views: 2653

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656942

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?

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:

Upvotes: 1

Related Questions