How to give ALIAS with current "month/year"?

Asked

Viewed 438 times

2

Considering the query below, I need the ALIAS where there are SOMAS, to be "nomeMes/anoAtual" ("ABR/14","MAI/14"...) dynamically.

    SELECT      fornecedor.codigo, UPPER(fornecedor.razaosocial) AS fornecedor, 
                FORMAT(SUM((SELECT IFNULL(SUM(rel_pedido.valortotal),0) FROM rel_pedido WHERE rel_pedido.cod_pedido = pedido.codigo AND YEAR(pedido.dt_pedido) = YEAR(NOW())   AND MONTH(pedido.dt_pedido) = 4)),2)  AS 'ABR/14',
                FORMAT(SUM((SELECT IFNULL(SUM(rel_pedido.valortotal),0) FROM rel_pedido WHERE rel_pedido.cod_pedido = pedido.codigo AND YEAR(pedido.dt_pedido) = YEAR(NOW())   AND MONTH(pedido.dt_pedido) = 5)),2)  AS 'MAI/14',
                FORMAT(SUM((SELECT IFNULL(SUM(rel_pedido.valortotal),0) FROM rel_pedido WHERE rel_pedido.cod_pedido = pedido.codigo AND YEAR(pedido.dt_pedido) = YEAR(NOW())   AND MONTH(pedido.dt_pedido) = 6)),2)  AS 'JUN/14',
                FORMAT(SUM((SELECT IFNULL(SUM(rel_pedido.valortotal),0) FROM rel_pedido WHERE rel_pedido.cod_pedido = pedido.codigo AND YEAR(pedido.dt_pedido) = YEAR(NOW())   AND MONTH(pedido.dt_pedido) = 7)),2)  AS 'JUL/14',
                FORMAT(SUM((SELECT IFNULL(SUM(rel_pedido.valortotal),0) FROM rel_pedido WHERE rel_pedido.cod_pedido = pedido.codigo AND YEAR(pedido.dt_pedido) = YEAR(NOW())   AND MONTH(pedido.dt_pedido) = 8)),2)  AS 'AGO/14',
                FORMAT(SUM((SELECT IFNULL(SUM(rel_pedido.valortotal),0) FROM rel_pedido WHERE rel_pedido.cod_pedido = pedido.codigo AND YEAR(pedido.dt_pedido) = YEAR(NOW())   AND MONTH(pedido.dt_pedido) = 9)),2)  AS 'SET/14',
                FORMAT(SUM((SELECT IFNULL(SUM(rel_pedido.valortotal),0) FROM rel_pedido WHERE rel_pedido.cod_pedido = pedido.codigo AND YEAR(pedido.dt_pedido) = YEAR(NOW())   AND MONTH(pedido.dt_pedido) = 10)),2) AS 'OUT/14',
                FORMAT(SUM((SELECT IFNULL(SUM(rel_pedido.valortotal),0) FROM rel_pedido WHERE rel_pedido.cod_pedido = pedido.codigo AND YEAR(pedido.dt_pedido) = YEAR(NOW())   AND MONTH(pedido.dt_pedido) = 11)),2) AS 'NOV/14',
                FORMAT(SUM((SELECT IFNULL(SUM(rel_pedido.valortotal),0) FROM rel_pedido WHERE rel_pedido.cod_pedido = pedido.codigo AND YEAR(pedido.dt_pedido) = YEAR(NOW())   AND MONTH(pedido.dt_pedido) = 12)),2) AS 'DEZ/14',
                FORMAT(SUM((SELECT IFNULL(SUM(rel_pedido.valortotal),0) FROM rel_pedido WHERE rel_pedido.cod_pedido = pedido.codigo AND YEAR(pedido.dt_pedido) = YEAR(NOW())+1 AND MONTH(pedido.dt_pedido) = 1)),2)  AS 'JAN/15',
                FORMAT(SUM((SELECT IFNULL(SUM(rel_pedido.valortotal),0) FROM rel_pedido WHERE rel_pedido.cod_pedido = pedido.codigo AND YEAR(pedido.dt_pedido) = YEAR(NOW())+1 AND MONTH(pedido.dt_pedido) = 2)),2)  AS 'FEV/15',
                FORMAT(SUM((SELECT IFNULL(SUM(rel_pedido.valortotal),0) FROM rel_pedido WHERE rel_pedido.cod_pedido = pedido.codigo AND YEAR(pedido.dt_pedido) = YEAR(NOW())+1 AND MONTH(pedido.dt_pedido) = 3)),2)  AS 'MAR_15'
    FROM        pedido
    INNER JOIN  fornecedor ON fornecedor.codigo     = pedido.fornecedor
    WHERE       pedido.fornecedor = 5
    GROUP BY    fornecedor.codigo
mysql sql select-sql string-concatenation

2 answers

2

You can generate a text file with a standard tab and add the first row manually with the columns you want. Usually this type of query is for report, so if your scenario allows, you can import the "csv" file in any excel of life.

1

It is not possible to define an alias dynamically. SQL is not your friend when it comes to getting columns from the database that you do not know what they are.

Consider creating a stored Procedure that generates the SQL command dynamically, by concatenating as alias a separately generated variable. Then you can get the result of stored Procedure in your application instead of sending this SELECT command.

Browser other questions tagged mysql sql select-sql string-concatenation

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