Turning rows into columns in SQL [duplicate]

Question

Turning rows into columns in SQL [duplicate]

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I have the following Query:

SELECT distinct finmovem.num_transacao
       ,finempe.codigo_orgao 
       ,finempe.cod_reduzido 
       ,finempe.num_empenho 
       ,finempe.data_empenho 
       ,finempe.nome_fornecedor 
       ,finempe.valor_empenhado 
       ,finempe.valor_anulado 
       ,finempe.valor_pago 
       ,finempe.valor_liquidado 
       ,cast(finempe.valor_empenhado as decimal(18,2)) - cast(finempe.valor_anulado as decimal(18,2)) - cast(finempe.valor_liquidado as decimal(18,2)) as sld_a_liquidar 
       ,cast(finempe.valor_empenhado as decimal(18,2)) - cast(finempe.valor_anulado as decimal(18,2)) - cast(finempe.valor_pago as decimal(18,2)) as sld_a_pagar
       ,cast(orcdotac.sld_orc_ano as decimal(18,2)) + cast(orcdotac.sld_orc_vinc as decimal(18,2)) + cast(orcdotac.sld_supl_ano as decimal(18,2)) + cast(orcdotac.sld_esp_ano as decimal(18,2)) + cast(orcdotac.sld_ext_ano as decimal(18,2)) - cast(orcdotac.sld_re_ano as decimal(18,2)) as saldo_dot_ant
       ,SUM(finmovem.valor_transacao ) tansacao
FROM finempe
INNER JOIN finmovem ON
    (finempe.num_empenho = finmovem.num_empenho) 
INNER JOIN orcdotac ON
    (finempe.cod_reduzido = orcdotac.cod_reduzido) 
WHERE finempe.codigo_tipo = 1 and finempe.codigo_orgao = 02 and finempe.data_empenho between '1-1-2002' and '31-12-2002' and finempe.cod_reduzido = 25
GROUP BY finmovem.num_transacao, finempe.codigo_orgao, finempe.cod_reduzido, finempe.num_empenho, finempe.data_empenho, finempe.nome_fornecedor, finempe.valor_empenhado, 
finempe.valor_anulado, finempe.valor_pago, finempe.valor_liquidado, orcdotac.sld_orc_ano, orcdotac.sld_orc_vinc, orcdotac.sld_supl_ano, orcdotac.sld_esp_ano, orcdotac.sld_ext_ano,
orcdotac.sld_re_ano   
ORDER BY finempe.cod_reduzido, finempe.num_empenho

That returns me the following result:

I would like the in-transaction and transaction to become columns instead of lines, that is, for each transaction_to have a corresponding transaction column, what modifications should be made? I want only one line for num_empenho 11.

sql sql-server

asked by anonymous 12.04.2018 / 18:40

2 answers

1

I hope this helps you. Do what you need. If you continue with doubts say.

link

12.04.2018 / 19:18

Is there anything wrong with this code? * ngFor client side

score 0 · Accepted Answer

I used the tips given in the post and I came up with the following answer:

SELECT num_empenho AS EMP, data_empenho AS DATA, nome_fornecedor AS FORNECEDOR, valor_empenhado AS EMPENHADO, valor_anulado AS ANULADO, [3] AS LIQ_PERIODO, valor_liquidado AS LIQUID_ACUM, [5] + [7] AS PAGO_PERIODO, valor_pago AS PAGO_ACUMUL
FROM (
      SELECT finmovem.num_transacao
       ,finmovem.valor_transacao
       ,finempe.codigo_orgao 
       ,finempe.cod_reduzido 
       ,finempe.num_empenho 
       ,finempe.data_empenho 
       ,finempe.nome_fornecedor 
       ,finempe.valor_empenhado 
       ,finempe.valor_anulado 
       ,finempe.valor_pago 
       ,finempe.valor_liquidado 
       ,cast(finempe.valor_empenhado as decimal(18,2)) - cast(finempe.valor_anulado as decimal(18,2)) - cast(finempe.valor_liquidado as decimal(18,2)) as sld_a_liquidar 
       ,cast(finempe.valor_empenhado as decimal(18,2)) - cast(finempe.valor_anulado as decimal(18,2)) - cast(finempe.valor_pago as decimal(18,2)) as sld_a_pagar
       ,cast(orcdotac.sld_orc_ano as decimal(18,2)) + cast(orcdotac.sld_orc_vinc as decimal(18,2)) + cast(orcdotac.sld_supl_ano as decimal(18,2)) + cast(orcdotac.sld_esp_ano as decimal(18,2)) + cast(orcdotac.sld_ext_ano as decimal(18,2)) - cast(orcdotac.sld_re_ano as decimal(18,2)) as saldo_dot_ant     
FROM finmovem
LEFT JOIN finempe ON
    (finempe.num_empenho = finmovem.num_empenho) 
INNER JOIN orcdotac ON
    (finempe.cod_reduzido = orcdotac.cod_reduzido) 
WHERE finmovem.codigo_tipo = 1 and finmovem.codigo_orgao = 02 and finmovem.data_transacao between '1-1-2002' and '31-12-2002' and finempe.cod_reduzido = 25
GROUP BY finmovem.valor_transacao, finmovem.num_transacao, finempe.codigo_orgao, finempe.cod_reduzido, finempe.num_empenho, finempe.data_empenho, finempe.nome_fornecedor, finempe.valor_empenhado, 
finempe.valor_anulado, finempe.valor_pago, finempe.valor_liquidado, orcdotac.sld_orc_ano, orcdotac.sld_orc_vinc, orcdotac.sld_supl_ano, orcdotac.sld_esp_ano, orcdotac.sld_ext_ano,
orcdotac.sld_re_ano   
      ) sq 
PIVOT (SUM(sq.valor_transacao) FOR num_transacao IN ( [3], [5], [7])) AS pt

The four generated lines turned this:

Thanks for the tips, guys!