Mysql Query - Transfer

Asked

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?

mysql select-sql select

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

  • It is in the same table...in these fields (expense.dismissePassage, expense.dismisseTaxi, expense.dismisseHotel, expense.dismissesReffection, expense.dismissesOther)

  • Never has more than one expense on the same line? The table structure was thought to have.

  • 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.

1 answer

2


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;

Browser other questions tagged mysql select-sql select

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