Turn rows into SQL columns

Question

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 a transaction and transaction turn columns instead of rows, that is, for each num_transaction to have a corresponding transaction column, what modifications should be made? I just want a line to number 11.

Answer 1

well I hope this helps you. Do what you need. If you still have doubts say.

https://technet.microsoft.com/en-us/library/ms177410(v=sql.105). aspx

Answer 2

I used the tips given in the post and arrived at 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 a lot for the tips, guys!