Mysql Query - Transfer

Question

Mysql Query - Transfer

Asked 10 years, 1 month ago

Viewed 35 times

0

I have the following appointment:

SELECT      despesa.codRequisicao,
            despesa.histDespesa,                
            despesa.despesaPassagem, despesa.despesaTaxi, despesa.despesaHotel, despesa.despesaRefeicao, despesa.despesaOutros
FROM        despesa;

Where the result displays this way:

1   hist1   0   200 0   0   0
1   hist2   0   0   600 0   0

I need the result to stay this way:

1   hist1   despesaTaxi    200
1   hist2   despesaHotel   600

How do you do?

Where do you have the information of "Farewell" and "Farewell"? It is in another table than the expense?

– CesarMiguel

2014/10/22 at 11:42
It is in the same table...in these fields (expense.dismissePassage, expense.dismisseTaxi, expense.dismisseHotel, expense.dismissesReffection, expense.dismissesOther)

– mcardoso

2014/10/22 at 11:49
Never has more than one expense on the same line? The table structure was thought to have.

– bfavaretto

2014/10/22 at 12:44
Without a doubt, it would be more interesting to rethink the structure, where it would have a column that saved the type of expenditure and one with the value of the expenditure, this would avoid this type of structure.

– Rodrigo Coelho

2014/10/23 at 11:04

1 answer

Browser other questions tagged mysql select-sql select

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

by Rodrigo Coelho • **371** points · Answer 1 · 2014-10-22T12:27:17+00:00

You are wanting to join two columns together. What you can do is add the two columns together and display as one, as follows:

SELECT  despesa.codRequisicao,
        despesa.histDespesa,                
        (despesa.despesaPassagem + 
         despesa.despesaTaxi + 
         despesa.despesaHotel +
         despesa.despesaRefeicao + 
         despesa.despesaOutros) AS DespesasGerais 
  FROM  despesa;

Now if you want to know what was the registered expense on the line, you can create a condition:

SELECT  despesa.codRequisicao,
        despesa.histDespesa,    
        IF(despesa.despesaPassagem > 0,'Passagem',
           IF(despesa.despesaTaxi > 0, 'DespesaTaxi',
              IF(despesa.despesaHotel > 0, 'DespesaHotel',
                 IF(despesa.despesaRefeicao > 0, 'DespesaRefeicao',
                    IF(despesa.despesaOutros > 0, 'DespesaOutros', 'Nenhuma Despesa')
                 )
              ) 
           )
        ) AS TipoDespesa,          
        (despesa.despesaPassagem + 
         despesa.despesaTaxi + 
         despesa.despesaHotel +
         despesa.despesaRefeicao + 
         despesa.despesaOutros) AS ValorDespesa 
  FROM  despesa;