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

Question

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

Navigation

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

2

Considering the query below, I need the ALIAS where the SUMS exists, stay as "current / current_name" ("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 sql-select

asked by anonymous 19.11.2014 / 20:24

2 answers

execute script with administrator permission How to instantiate primary key in another MVC class?

score 2 · Answer 1

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

score 1 · Answer 2

You can not dynamically define an alias. SQL is not your friend when it comes to getting bank columns that you do not know what they are.

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