Group records and create column in SQL Firebird

Asked

Viewed 211 times

0

I have the following records:

|MAQUINA|LOTECA| DTPOST |SEXOCAR|QTDEPIN|
|17 MAQ |602121|02/04/20|Fêmeas | 9300  |
|17 MAQ |602121|02/04/20|Machos | 9600  |
|17 MAQ |602121|02/04/20|Fêmeas | 100   |
|18 MAQ |509119|30/03/20|Machos | 2000  |
|18 MAQ |509119|30/03/20|Fêmeas | 9600  |
|18 MAQ |509119|01/04/20|Fêmeas | 6300  |

I’d like it to stay that way:

|MAQUINA|LOTECA| DTPOST |FEMEAS| MACHOS|
|17 MAQ |602121|02/04/20| 9400 | 9600  |
|18 MAQ |509119|30/03/20| 9600 | 2000  |
|18 MAQ |509119|01/04/20| 6300 | NULL  |

I’m assembling the SQL:

select MAQUINA,LOTECA,DTPOST,SEXOCAR,SUM(QTDEPIN) AS QTDE FROM TB_TESTE 
GROU BY MAQUINA,LOTECA,DTPOST,SEXOCAR
ORDER BY MAQUINA,DTPOST

My question is how to create the columns and join the information.

sql firebird

  • Search for PIVOT https://www.devmedia.com.br/forum/transforma-linhas-em-colunas-no-firebird/455967

  • You don’t need to put "solved" in the title. I know it’s common in many forums, but here it works different. In your case, as you yourself found the solution, just use the field of answers below (the textarea "Your Answer"), and then enough mark it as accepted, That’s enough to indicate that it’s been resolved.

1 answer

0


I decided as follows:

select MAQUINA,LOTECA,DTPOST,
 SUM(case when SEXOCARR = 'Fêmeas' then QTDEPINT else 0 end) as FEMEAS,
 SUM(case when SEXOCARR = 'Machos' then QTDEPINT else 0 end) as MACHOS FROM TB_TESTE 
GROU BY MAQUINA,LOTECA,DTPOST
ORDER BY MAQUINA,DTPOST

Browser other questions tagged sql firebird

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