Create a column for each row returned from Select

Question

Create a column for each row returned from Select

Navigation

#1 by (2 votes)

1

Yesterday I posted a question about how to return something even if there are no records found, and with the help of @RovannLinhalis we get the following query :

with dados as ( 
SELECT 
public.controleimpressoes.codigo, 
public.impressoras.patrimonio, 
public.impressoramodelo.modelo, 
public.impressoralocal.descricao, 
data, 
diferecacontador, 
diferecacontadordigital, 
diferencacontadorcolor, 
public.empresa.nome, 
datainicial, 
datafinal 
FROM 
public.controleimpressoes, public.impressoras, public.impressoramodelo, 
public.impressoralocal, public.empresa 
where 
public.controleimpressoes.codigoempresa = public.empresa.codigo and 
public.controleimpressoes.codigoimpressora = public.impressoras.codigo and 
public.impressoras.codigomodelo = public.impressoramodelo.codigo and 
public.controleimpressoes.codigoimpressoralocal = public.impressoralocal.codigo and 
data between '2017-01-01' and '2017-12-31' and 
public.impressoras.patrimonio between '1000' and '1882' and 
public.empresa.nome between 'A' and 'B' and 
public.impressoramodelo.modelo between 'C748' and 'C748' 
), meses as ( 

Select distinct 
intervalo, 
d.patrimonio, 
d.modelo, 
d.descricao, 
d.nome 
FROM generate_series('2017-01-01'::timestamp, '2017-12-31'::timestamp, '1 Month') as intervalo 
CROSS JOIN dados d) 


SELECT DISTINCT 
to_char(intervalo , 'TMMon') dataMes, 
m.*, 
(case when d.diferecacontador is null then 0 else d.diferecacontador end), 
d.diferecacontadordigital, 
d.diferencacontadorcolor, 
d.datainicial, 
d.datafinal 
FROM meses m 
LEFT OUTER JOIN DADOS d on date_trunc('month',d.data) = m.intervalo and m.patrimonio = d.patrimonio 
order by m.nome, m.patrimonio, intervalo

That returned the data I needed.

But as I'm using this select to generate a report in JasperStudio, my need would be to turn this return to something like this:

Pat. | Mod  | Setor Inst.    | Empresa              | M1  |C1 | M2  |C2 | M3  |C3 | M4  |C4 | M5  |C5    | M6  |C6   | M7  |C7  | M8  |C8 | M9  |C9 | M10 |C10| M11 |C11| M12 |C12
1854 | C748 | 4º andar - 401 | A                    | Jan | 0 | Fev | 0 | Mar | 0 | Abr | 0 | Mai | 2409 | Jun | 551 | Jul | 39 | Ago | 0 | Set | 0 | Out | 0 | Nov | 0 | Dez | 0




select * from crosstab (
'with dados as ( 
SELECT 
public.impressoras.patrimonio, 
public.impressoramodelo.modelo, 
public.impressoralocal.descricao, 
data, 
diferecacontador, 
diferecacontadordigital, 
diferencacontadorcolor, 
public.empresa.nome, 
datainicial, 
datafinal 
FROM 
public.controleimpressoes, public.impressoras, public.impressoramodelo, 
public.impressoralocal, public.empresa 
where 
public.controleimpressoes.codigoempresa = public.empresa.codigo and 
public.controleimpressoes.codigoimpressora = public.impressoras.codigo and 
public.impressoras.codigomodelo = public.impressoramodelo.codigo and 
public.controleimpressoes.codigoimpressoralocal = public.impressoralocal.codigo and 
data between \'2017-01-01\' and \'2017-12-31\' and 
public.impressoras.patrimonio between \'1000\' and \'1882\' and 
public.empresa.nome between \'A\' and \'B\' and 
public.impressoramodelo.modelo between \'C748\' and \'C748\'
), meses as ( 

Select distinct 
intervalo, 
d.patrimonio, 
d.modelo, 
d.descricao, 
d.nome 
FROM generate_series(\'2017-01-01\'::timestamp, \'2017-12-31\'::timestamp, \'1 Month\') as intervalo 
CROSS JOIN dados d)


SELECT DISTINCT 
to_char(intervalo , \'TMMon\') dataMes, 
m.*, 
(case when d.diferecacontador is null then 0 else d.diferecacontador end), 
d.diferecacontadordigital, 
d.diferencacontadorcolor, 
d.datainicial, 
d.datafinal 
FROM meses m 
LEFT OUTER JOIN DADOS d on date_trunc(\'month\',d.data) = m.intervalo and m.patrimonio = d.patrimonio 
order by m.nome, m.patrimonio, intervalo') as final_result (Pat text, Modelo text, Descricao text, datas timestamp, diferecacontador numeric, diferecacontadordigital numeric, diferencacontadorcolor numeric, empresa text, datainicial timestamp, datafinal timestamp)

postgresql

asked by anonymous 09.03.2018 / 12:43

1 answer

How to add a column at page runtime? Group sum by month and year

score 2 · Accepted Answer

I think your end result would be this:

select * from crosstab ( 
'with dados as ( 
SELECT 
public.impressoras.patrimonio, 
public.impressoramodelo.modelo, 
public.impressoralocal.descricao, 
data, 
diferecacontador, 
diferecacontadordigital, 
diferencacontadorcolor, 
public.empresa.nome, 
datainicial, 
datafinal 
FROM 
public.controleimpressoes, public.impressoras, public.impressoramodelo, 
public.impressoralocal, public.empresa 
where 
public.controleimpressoes.codigoempresa = public.empresa.codigo and 
public.controleimpressoes.codigoimpressora = public.impressoras.codigo and 
public.impressoras.codigomodelo = public.impressoramodelo.codigo and 
public.controleimpressoes.codigoimpressoralocal = public.impressoralocal.codigo and 
data between ''2017-01-01'' and ''2017-12-31'' and 
public.impressoras.patrimonio between ''1000'' and ''1882'' and 
public.empresa.nome between ''4All Tecnologia Ltda'' and ''4All Tecnologia Ltda'' and 
public.impressoramodelo.modelo between ''C748'' and ''C748'' 
), meses as ( 

Select distinct 
intervalo, 
d.patrimonio, 
d.modelo, 
d.descricao, 
d.nome 
FROM generate_series(''2017-01-01''::timestamp, ''2017-12-31''::timestamp, ''1 Month'') as intervalo 
CROSS JOIN dados d) 


SELECT DISTINCT 
m.patrimonio, 
to_char(intervalo , ''TMMon'') dataMes, 
coalesce(d.diferecacontador,0) as valor 
FROM meses m 
LEFT OUTER JOIN DADOS d on date_trunc(''month'',d.data) = m.intervalo and m.patrimonio = d.patrimonio 
order by 1,2') 
as final_result (
patrimonio varchar,  
 "Jan" numeric, 
 "Fev" numeric, 
 "Mar" numeric, 
 "Abr" numeric, 
 "Mai" numeric ,
 "Jun" numeric, 
 "Jul" numeric, 
 "Ago" numeric, 
 "Set" numeric, 
 "Out" numeric, 
 "Nov" numeric, 
 "Dez" numeric);