Group rows with null

Question

Group rows with null

Navigation

#1 by (1 votes)

1

How can I remove null rows and leave dates only side by side;

Example: 48010255 | 2016-06-10 07: 46: 41,573 | null | 2016-06-09 12: 36: 22,243

SELECT
     cod_pista
     ,DT_ENTRADA    = IIF(ST_TIPO_ACAO = 'E' AND CO_DESTINO = 7888,[DATA],NULL)
     ,DT_SAIDA      = IIF(ST_TIPO_ACAO = 'S' AND CO_ORIGEM = 7888,[DATA],NULL)
     ,DT_ENVIO      = IIF(ST_TIPO_ACAO = 'S' AND CO_DESTINO = 7888,[DATA],NULL)
FROM tmp_2 TR

Thank you.

sql-server

asked by anonymous 16.08.2016 / 07:31

1 answer

How to hide undefined or validated variables after reloading the page? How to know the type of the DOM object

score 1 · Accepted Answer

Only with SQL Standard can not resolve this. It is necessary to use analytical functions to analyze the data that will come in the next line, or that came in the previous line.

This is a case for using the LAG function of SQL Server.

LAG (scalar_expression [,offset] [,default])  
    OVER ( [ partition_by_clause ] order_by_clause )

The function LAG can "see" the value of the next line of the query, so we can make some decisions.

In the case of your query, I believe that if you do something like this:

WITH TR (SELECT id,
                cod_pista,
                DT_ENTRADA = IIF(ST_TIPO_ACAO = 'E' AND CO_DESTINO = 7888, [DATA], NULL),
                DT_SAIDA   = IIF(ST_TIPO_ACAO = 'S' AND CO_ORIGEM  = 7888, [DATA], NULL),
                DT_ENVIO   = IIF(ST_TIPO_ACAO = 'S' AND CO_DESTINO = 7888, [DATA], NULL)
           FROM tmp_2)



SELECT DISTINCT
       cod_pista,
       DT_ENTRADA = ISNULL(DT_ENTRADA, LAG(DT_ENTRADA, 1, 0) OVER (ORDER BY id)),
       DT_SAIDA = ISNULL(DT_SAIDA, LAG(DT_SAIDA, 1, 0) OVER (ORDER BY id)),
       DT_ENVIO = ISNULL(DT_ENVIO, LAG(DT_ENVIO, 1, 0) OVER (ORDER BY id))
 FROM tr

See how the return of this query is, you will certainly need some design adjustments, but you will already have your data "grouped" on the same line.