Create a column for each row returned from Select

Asked

Viewed 42 times

1

Yesterday I posted a question regarding how to return something even if there are no records found, and with the help of @Rovannlinhalis we managed to 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 me the data I needed.

inserir a descrição da imagem aqui

But since I’m using this select to generate a report in Jasperstudio, my need would be to turn that feedback to something similar to 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

  • You want to turn the result into pivot table?

  • knowing the amount of columns until you can do rsrs complicates when the quantity is dynamic

  • @Rovannlinhalis The number of columns would always be the same at first

  • as @Laerte said, you need the pivot table, take a look there, when I release here...if I have not managed to help you try to help you

  • @Rovannlinhalis thanks, I will search for pivot table and see if I can implement in my need

  • I think it is possible to even do this in the report tool... look for cross tab in it too

  • @Laerte thanks for the tip, I will search how to use the pivot table :)

  • @Laerte updated my question as the way I tried to accomplish the pivot table but there was a mistake, you know tell me what would be wrong?

  • you have to make Crosstab in the whole select... not in the first just...rs

  • Let’s go continue this discussion in chat.

  • @R.Santos unfortunately I can’t help you now, but Rovann I believe I can help you.

Show 6 more comments

1 answer

2


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);

  • It worked out Rovann Thanks so much for the help, just a question, I could add more items in this query as name and template for example?

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.