Concatenate the field and group the rest

Asked

Viewed 143 times

2

I have the following query:

SELECT finempe.data_empenho
       , finempe.num_empenho
       , finhisem.quantidade
       , finhisem.historico
       , finhisem.valor
       , finhisem.valor_total
FROM finempe
RIGHT JOIN finhisem ON
    (finhisem.num_empenho = finempe.num_empenho)
WHERE finempe.data_empenho between '01-01-2002' and '31-12-2002' and  finempe.valor_empenhado != finempe.valor_anulado and finempe.codigo_tipo = 1 and finhisem.codigo_tipo = 1 and conta_desp = 31900100 and finhisem.ano_empenho = 2002
ORDER BY finempe.num_empenho, finhisem.num_seq

I want to group the lines, but concatenating the field historical (I imagine doing a SUM on value and total value and a concatenation on historical)

inserir a descrição da imagem aqui

Example grouping row 5, 6 and 7:
"2002-01-02" "45" "10000" "Provision of p/pagto resources. complementary to retired servers linked to FUNCAPI, p/ the period from January to December/2002" "540000" "540000"

that is, keep all values and concatenate the history

php sql html5 sql-server

  • Hello! What have you tried to do so far?

  • Complete the question by telling which fields you want to group. Put an example of how the expected result would be and also put some attempt you have already made and did not work as expected.

  • What you want to concatenate in the historical field?

  • 1

    @Wellingtonaraujo I want to concatenate the historical field of line 1 with line 2 and keep the rest as if it were a group by

2 answers

1


I ended up solving the problem through PHP, as I would display in a table in a browser able to use some artifacts, I did as follows:

                                       $linha = sqlsrv_fetch_array($resultado);

                                       $data = date_format($linha["data_empenho"], 'd/m/y');
                                       $num_empenho_ant = $num_empenho = $linha["num_empenho"];
                                       $historico = $linha["historico"];
                                       $quantidade = $linha["quantidade"];
                                       $unidade = $linha["unidade"];
                                       $valor = $linha["valor"];
                                       $valor_anulado += $linha["valor_anulado"];
                                       $valor_total = $linha["valor_total"];
                                       $total = $linha["valor_total"];

                                        while ($linha)//COLOCA OS VALORES EM VARIÁVEIS (QUANDO "num_empenho" for diferente) PARA PERMITIR A CONCATENAÇÃO DO HISTORICO E IMPRIMO-LO COMPLETO (QUANDO "num_empenho" for igual)
                                        {
                                            if($linha["num_empenho"] == $num_empenho_ant)
                                            {                                               
                                               $historico .=  " ".$linha["historico"];        

                                            }
                                            else{  
                                                ?>
                                                <tr class="odd gradeA">                                                    
                                                        <td align = "right">  <?php echo $data; ?> </td>                                                    
                                                        <td align = "center"> <?php echo $num_empenho; ?> </td>
                                                        <td align = "center"> <?php echo $unidade; ?> </td>
                                                        <td align = "center"> <?php echo $unidade; ?> </td>
                                                        <td align = "center"> <?php echo $historico; ?> </td>
                                                        <td align = "right">  <?php echo number_format($valor, 2, ',', '.');?> </td>
                                                        <td align = "right">  <?php echo number_format($valor_total, 2, ',', '.');?> </td>
                                                </tr>
                                   <?php        
                                                $num_empenho_ant = $linha["num_empenho"];                                                

                                                $data = date_format($linha["data_empenho"], 'd/m/y');
                                                $num_empenho = $linha["num_empenho"];
                                                $historico = $linha["historico"];
                                                $quantidade = $linha["quantidade"];
                                                $unidade = $linha["unidade"];                                                
                                                $valor = $linha["valor"];
                                                $valor_total = $linha["valor_total"];
                                                $total += $linha["valor_total"];
                                                $valor_anulado += $linha["valor_anulado"];

                                               }

                                               $linha = sqlsrv_fetch_array($resultado);
                                        }

  • Top... is basically the logic I used.... I sent in SQL because you asked in SQL....

1

Good afternoon, I had to use a "while" to do what you need. I used an sql schema called "CURSOR". Follow the example, run there and tell me if it worked:

IF OBJECT_ID('TEMPDB..#DadosTeste') IS NOT NULL DROP TABLE #DadosTeste
CREATE TABLE #DadosTeste(data_empenho datetime, num_empenho int, quantidade int, historico varchar(800), valor varchar(800), valor_total varchar(800))

IF OBJECT_ID('TEMPDB..#DadosHisto') IS NOT NULL DROP TABLE #DadosHisto
CREATE TABLE #DadosHisto(num_empenho int, histor varchar(800))

insert #DadosTeste values('2002-01-02', 37, 1000, 'TESTE DE TESTE DE TESTE', '1208200','1208200')
insert #DadosTeste values('2002-01-02', 37, 0, '2002', '0','0')

insert #DadosTeste values('2002-01-02', 38, 1000, 'TESTE PARA QUALQUER COISA', '900000','900000')
insert #DadosTeste values('2002-01-02', 38, 0, 'ANO 2002', '0','0')

insert #DadosTeste values('2002-01-02', 45, 1000, 'VAMOS QUE VAMOS', '540000','540000')
insert #DadosTeste values('2002-01-02', 45, 0, 'FUNDACAO DO TESTE', '0','0')

DECLARE @NUMG_EMPENHO INT
DECLARE @HISTORICO VARCHAR(800)

DECLARE CUR_Cartoes CURSOR FOR
    SELECT 
        num_empenho
        ,historico
    FROM 
        #DadosTeste

open CUR_Cartoes

FETCH NEXT FROM CUR_Cartoes INTO @NUMG_EMPENHO, @HISTORICO

WHILE (@@FETCH_STATUS = 0) BEGIN

    if exists(SELECT num_empenho FROM #DadosHisto where num_empenho = @NUMG_EMPENHO) BEGIN
        UPDATE #DadosHisto SET histor = (SELECT histor FROM #DadosHisto where num_empenho = @NUMG_EMPENHO) + ' ' + @HISTORICO WHERE num_empenho = @NUMG_EMPENHO
    END ELSE BEGIN
        INSERT #DadosHisto VALUES(@NUMG_EMPENHO, @HISTORICO)
    END

    FETCH NEXT FROM CUR_Cartoes INTO @NUMG_EMPENHO, @HISTORICO

END

CLOSE CUR_Cartoes
DEALLOCATE CUR_Cartoes

SELECT 
    TE.data_empenho
    ,DA.num_empenho
    ,TE.quantidade
    ,DA.histor
    ,TE.valor
    ,TE.valor_total 
FROM 
    #DadosHisto DA
    INNER JOIN #DadosTeste TE ON DA.num_empenho = TE.num_empenho
WHERE
    TE.valor <> '0'

  • vc modifies the BD by "joining" the historical field and then makes a simple select?

  • 1

    I did the following, I created a temporary one, then inside the "While" I went including in this temporary the data of the column "Historico" and concatenating, and finally I do an "Inner" with the original table showing the result.

Browser other questions tagged php sql html5 sql-server

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