MySQL - Select columns from a dynamic subselect

Question

MySQL - Select columns from a dynamic subselect

Navigation

#1 by (1 votes)

2

Doubt: How do I select and add dynamic fields?

I have a dynamic query:

SELECT *
FROM (
      SELECT @TotalSpendMensal
     ) AS t

The result of the @TotalSpendMensal variable is dynamic, that is, it returns for example "JAN / 2015", "FEV / 2015" or more months together with their values. Returning this way:

CentroCusto| JAN/2015 | FEV/2015 |...
CC1000     | 100.00   | 200.00   |...
CC2000     | 320.30   | 500.50   |...

I need this in the first select to return a grouped total, thus:

CentroCusto| JAN/2015 | FEV/2015 |...
TOTAL      | 420.30   | 700.50   |...

The problem is that, dynamically, I am not able to select the fields individually, to make the sum.

Is there any way to do this?

mysql

asked by anonymous 02.03.2015 / 15:57

1 answer

Calculate clicks per second How to do Filter after pressing any key on the keyboard, ENTER for example

score 1 · Answer 1

You can create standardized aliases (A, B, C, etc.) for the columns to be totalized in @TotalSpendMensal

SELECT *
FROM (
      SELECT 'MAR/2015' as A, 'ABR/2015' as B FROM @TotalSpendMensal
     ) AS t

and then format as desired

SELECT SUM(A) as 'MAR/2015', SUM(B) as 'ABR/2015'
FROM (
      SELECT 'MAR/2015' as A, 'ABR/2015' as B FROM @TotalSpendMensal
     ) AS t