Concatenate the field and group the remainder

Question

Concatenate the field and group the remainder

Navigation

#1 by (1 votes)
#2 by (1 votes)

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 rows, but concatenating the historical field (I imagine doing a SUM in value and total-value and concatenation in < in> historical )

Examplegroupingline5,6and7:
"2002-01-02" "45" "10000" "Provision of resources for the complementary payment to retired employees linked to FUNCAPI, for the period from January to December 2002" "540000" "540000"

ie keep all values and concatenate history

php sql sql-server html5

asked by anonymous 26.04.2018 / 18:14

2 answers

1

Good afternoon, I had to use a while to do what you need. I used an sql schema called "CURSOR". Here's the example, run there and tell me 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'

26.04.2018 / 20:42

Segmentation Fault When Removing List Occurrences What is the most efficient way to get information on any website and use it in an Android APP?

score 1 · Accepted Answer

I just solved the problem through PHP, as I would display in a table in a browser to be able to use some artimanhas, I did the following:

                                       $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);
                                        }