Mysql - Selecting columns from a dynamic subselect

Asked

Viewed 462 times

2

Doubt: How to select and add dynamic fields?

I have a dynamic consultation:

SELECT *
FROM (
      SELECT @TotalSpendMensal
     ) AS t

The result of the variable @Totalspendmonthly is dynamic, ie returna for example "JAN/2015", "FEB/2015" or more months along with its values. Returning in this way:

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

I need this first select to return a grouped total, so:

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

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

Is there any way to do that?

mysql

  • Statement of @TotalSpendMensal?

  • No, @Totalspendmonthly is a result of a dynamic statement.

  • I think the only solution will be to make this query another dynamic query as well.

1 answer

1

You can create standard aliases (A,B,C,etc...) for columns to be totaled in @Totalspendmonthly

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

Browser other questions tagged mysql

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