none
Rellenar columnas NULL con valores de lineas anteriores RRS feed

  • Pregunta

  • Un saludo a todos

    Despues de importar un archivo excel al estar las celdas de las 2 primeras columnas combinadas la tabla resultante me queda de esta forma:


    DECLARE @t TABLE (c1 char(1), c2 char(2), c3 int)
    INSERT INTO @t VALUES ('A', 'A1',1),(null , null ,2),(null , 'A2' ,3),(null , null ,4)
    ,('B', 'B1' ,5),(null, null ,6),(null, 'B2',null),(null, 'B3',6)
    SELECT * FROM @t

    quiero entonces construir una select de forma tal que pueda obtener:

    c1 c2 c3
    -----------------
    A A1 1
    A A1 2
    A A2 3
    A A2 4
    B B1 1
    B B1 2
    B B2 NULL
    B B3 3

    Con un cursor y reescribiéndola, por ejemplo en una temporal, preguntando si el registro anterior es NULL o no, etc. se resuelve el problema. Pero como el uso de cursores debe evitarse siempre que se pueda ¿ alguna otra forma de lograrlo ?. Esto en Sql2008 R2.


    Muchas gracias de antemano

    lunes, 27 de marzo de 2017 14:35

Respuestas

  • A que te refieres cuando dices "registro anterior"?

    Recuerda que las tablas no tienen un orden predeterminado por lo que hablar de registro anterior no tiene mucho sentido en este caso, al menos que podamos usar una logica de ordenamiento.

    Por ejemplo, supongamos que la tabla tiene una columna con propiedad IDENTITY la cual podemos usar para seguir el orden cronologico en las filas se insertaron:

    DECLARE @t table (
         sk int NOT NULL
                IDENTITY
                PRIMARY KEY,
         c1 char(1),
         c2 char(2),
         c3 int
        );
    
    INSERT INTO @t
        (c1, c2, c3)
    VALUES
    	('A', 'A1', 1),
    	(NULL, NULL, 2),
    	(NULL, 'A2', 3),
    	(NULL, NULL, 4),       
    	('B', 'B1', 5),
    	(NULL, NULL, 6),
    	(NULL, 'B2', NULL),
    	(NULL, 'B3', 6);
    
    SELECT
        COALESCE(A.c1, B.c1) AS c1,
    	COALESCE(A.c2, D.c2) AS c2,
    	A.c3
    FROM
        @t AS A
    	OUTER APPLY
        (
    	SELECT TOP (1)
    		C.*
    	FROM
    		@t AS C
    	WHERE
    		C.sk <= A.sk
    		AND C.c1 IS NOT NULL
    	ORDER BY
    		C.sk DESC
    	) AS B
    	OUTER APPLY
        (
    	SELECT TOP (1)
    		C.*
    	FROM
    		@t AS C
    	WHERE
    		C.sk <= A.sk
    		AND C.c2 IS NOT NULL
    	ORDER BY
    		C.sk DESC
    	) AS D
    ORDER BY
    	A.sk;
    GO



    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas


    lunes, 27 de marzo de 2017 15:00
  • en SQL Server 2012 y superiores quizá pudiéramos usar lag, pero en 2008 r2 , no así que te propongo este ejercicio, no se muy bien que quieres hacer con c3 así que lo dejo tal cual esperandoqu te sirva

    DECLARE @t TABLE (id int identity(1,1),c1 char(1), c2 char(2), c3 int)
    INSERT INTO @t VALUES ('A', 'A1',1),(null , null ,2),(null , 'A2' ,3),(null , null ,4)
    ,('B', 'B1' ,5),(null, null ,6),(null, 'B2',null),(null, 'B3',6);
    SELECT id,
    case when c1 is null then 
        substring((select max( cast(id as char(20))+c1)  from @t t1 where t1.id<t.id ),21,1)
    else c1 end as c1  ,
    case when c2 is null then 
        substring((select max( cast(id as char(20))+c2)  from @t t1 where t1.id<t.id ),21,2)
    else c2 end as c2 ,c3 FROM @t t


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    lunes, 27 de marzo de 2017 15:12
    Moderador

Todas las respuestas

  • A que te refieres cuando dices "registro anterior"?

    Recuerda que las tablas no tienen un orden predeterminado por lo que hablar de registro anterior no tiene mucho sentido en este caso, al menos que podamos usar una logica de ordenamiento.

    Por ejemplo, supongamos que la tabla tiene una columna con propiedad IDENTITY la cual podemos usar para seguir el orden cronologico en las filas se insertaron:

    DECLARE @t table (
         sk int NOT NULL
                IDENTITY
                PRIMARY KEY,
         c1 char(1),
         c2 char(2),
         c3 int
        );
    
    INSERT INTO @t
        (c1, c2, c3)
    VALUES
    	('A', 'A1', 1),
    	(NULL, NULL, 2),
    	(NULL, 'A2', 3),
    	(NULL, NULL, 4),       
    	('B', 'B1', 5),
    	(NULL, NULL, 6),
    	(NULL, 'B2', NULL),
    	(NULL, 'B3', 6);
    
    SELECT
        COALESCE(A.c1, B.c1) AS c1,
    	COALESCE(A.c2, D.c2) AS c2,
    	A.c3
    FROM
        @t AS A
    	OUTER APPLY
        (
    	SELECT TOP (1)
    		C.*
    	FROM
    		@t AS C
    	WHERE
    		C.sk <= A.sk
    		AND C.c1 IS NOT NULL
    	ORDER BY
    		C.sk DESC
    	) AS B
    	OUTER APPLY
        (
    	SELECT TOP (1)
    		C.*
    	FROM
    		@t AS C
    	WHERE
    		C.sk <= A.sk
    		AND C.c2 IS NOT NULL
    	ORDER BY
    		C.sk DESC
    	) AS D
    ORDER BY
    	A.sk;
    GO



    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas


    lunes, 27 de marzo de 2017 15:00
  • en SQL Server 2012 y superiores quizá pudiéramos usar lag, pero en 2008 r2 , no así que te propongo este ejercicio, no se muy bien que quieres hacer con c3 así que lo dejo tal cual esperandoqu te sirva

    DECLARE @t TABLE (id int identity(1,1),c1 char(1), c2 char(2), c3 int)
    INSERT INTO @t VALUES ('A', 'A1',1),(null , null ,2),(null , 'A2' ,3),(null , null ,4)
    ,('B', 'B1' ,5),(null, null ,6),(null, 'B2',null),(null, 'B3',6);
    SELECT id,
    case when c1 is null then 
        substring((select max( cast(id as char(20))+c1)  from @t t1 where t1.id<t.id ),21,1)
    else c1 end as c1  ,
    case when c2 is null then 
        substring((select max( cast(id as char(20))+c2)  from @t t1 where t1.id<t.id ),21,2)
    else c2 end as c2 ,c3 FROM @t t


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    lunes, 27 de marzo de 2017 15:12
    Moderador
  • Miguel,

    La funcion LAG no nos deja excluir valores nulos por ahora.

    Pego este pedido en Microsoft Connect, hecho por Itzik Ben-Gan, para que se aniada esta funcionalidad. Voten si creen que esta funcionalidad seria de ayuda tenerla en el lenguaje T-SQL.

    https://connect.microsoft.com/SQLServer/Feedback/Details/3130482

    Cuando la tengamos entonces se podria resolver asi:

    select
        sk,
        COALESCE(c1, LAG(c1) IGNORE NULLS OVER(ORDER BY sk)) AS c11,
        COALESCE(C2, LAG(c2) IGNORE NULLS OVER(ORDER BY sk)) AS c21,
        c3
    from
        @T;

    Con la version actual todavia se puede usar el metodo de concatenacion y funcion de ventana.

    -- 2012 / 2014 / 2016
    SELECT
    	sk,
        COALESCE(c1, CAST(SUBSTRING(MAX(CAST(sk AS binary(4)) + CAST(c1 AS binary(1))) OVER(ORDER BY sk ROWS UNBOUNDED PRECEDING), 5, 1) AS char(1))) AS c11,
        COALESCE(c2, CAST(SUBSTRING(MAX(CAST(sk AS binary(4)) + CAST(c2 AS binary(2))) OVER(ORDER BY sk ROWS UNBOUNDED PRECEDING), 5, 2) AS char(2))) AS c21,
        c3
    FROM
    	@T
    GO


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas



    lunes, 27 de marzo de 2017 15:52
  • Muchas gracias Hunchback, y tienes mucha razón en lo de "registro anterior" pero a los novatos se nos olvida. Después de ver tu solución como la tabla se construye en el momento de la importación pensé que tendría que modificar la estructura de la tabla ya creada agregándole el campo IDENTITY. Mirando las opciones en "Import Data" se puede crear esta antes e incluir el nuevo campo de esa forma y mediante el IDENTITY queda en el mismo "orden" que la hoja excel. Después de esto lo que me propones va perfecto.

    Saludos


    lunes, 27 de marzo de 2017 16:07
  • en SQL Server 2012 y superiores quizá pudiéramos usar lag, pero en 2008 r2 , no así que te propongo este ejercicio, no se muy bien que quieres hacer con c3 así que lo dejo tal cual esperandoqu te sirva

    DECLARE @t TABLE (id int identity(1,1),c1 char(1), c2 char(2), c3 int)
    INSERT INTO @t VALUES ('A', 'A1',1),(null , null ,2),(null , 'A2' ,3),(null , null ,4)
    ,('B', 'B1' ,5),(null, null ,6),(null, 'B2',null),(null, 'B3',6);
    SELECT id,
    case when c1 is null then 
        substring((select max( cast(id as char(20))+c1)  from @t t1 where t1.id<t.id ),21,1)
    else c1 end as c1  ,
    case when c2 is null then 
        substring((select max( cast(id as char(20))+c2)  from @t t1 where t1.id<t.id ),21,2)
    else c2 end as c2 ,c3 FROM @t t


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    Gracias Miguel, tu solución también me ha servido. Saludos 
    lunes, 27 de marzo de 2017 16:14