Supongamos que tengo la siguiente tabla de "valores" en mi base de datos SQL Server (2012):

Tabla1:

Id   Col1   Col2   Col3   Col4

Y quiero crear una segunda tabla de "anulación" que almacenará valores para anular los valores originales en caso de que un usuario necesite hacerlo. Entonces, dada la tabla anterior, la tabla de anulación se vería de la siguiente manera:

Anulaciones:

FK_Id   Col1   Col2   Col3   Col4   When_Inserted

Donde Overrides.FK_Id hace referencia a Table1.Id como una clave foránea.

Entonces, por ejemplo, supongamos que mi tabla Overrides tiene las siguientes filas dentro con anulaciones para una fila en Table1 con Id=1:

FK_Id:     Col1:             Col2:             Col3:              Col4:       When_Inserted:
1          Val1_1            Val2_1            Expected_Val3      NULL        1-Jan
1          NULL              Val2_2            NULL               NULL        2-Jan
1          NULL              Expected_Val2     NULL               NULL        3-Jan
1          Expected_Val1     NULL              NULL               NULL        4-Jan

Luego, según la columna When_Inserted: para que las últimas inserciones tengan prioridad, me gustaría que las anulaciones sean las siguientes:

FK_Id:     Col1:             Col2:             Col3:              Col4:
1          Expected_Val1     Expected_Val2     Expected_Val3      NULL 

Estoy tratando de pensar en una forma inteligente de crear este SQL y se me ocurre una solución bastante fea como:

SELECT
     FK_Id
    ,(
        SELECT TOP 1
            Col1
        FROM
            Overrides O1
        WHERE
            Col1 IS NOT NULL
            AND O1.FK_Id = O.FK_Id
        ORDER BY
            O1.When_Inserted DESC
      ) Col1

      ....  <same for each of the other columns>  ....

FROM
    Overrides O
GROUP BY
    FK_Id

Estoy seguro de que tiene que haber una mejor manera que sea más limpia y sustancialmente más eficiente.

3
John Bustos 26 oct. 2017 a las 20:24

3 respuestas

La mejor respuesta

Utilizando una expresión de tabla común con row_number() (último primero), cross apply() para desconectar sus columnas, filtre la última de cada columna (rn = 1) y finalmente pivot() volver a la misma forma:

;with cte as (
select o.fk_id, v.Col, v.Value, o.When_Inserted
  , rn = row_number() over (partition by o.fk_id, v.col order by o.when_inserted desc)
from overrides o
  cross apply (values('Col1',Col1),('Col2',Col2),('Col3',Col3),('Col4',Col4)
    ) v (Col,Value)
where v.value is not null
)
select fk_id, col1, col2, col3, col4
from (
  select fk_id, col, value
  from cte 
  where rn = 1
  ) s
pivot (max(Value) for Col in (col1,col2,col3,col4)) p

Demo de rextester: http://rextester.com/KGM96394

Devoluciones:

+-------+---------------+---------------+---------------+------+
| fk_id |     col1      |     col2      |     col3      | col4 |
+-------+---------------+---------------+---------------+------+
|     1 | Expected_Val1 | Expected_Val2 | Expected_Val3 | NULL |
+-------+---------------+---------------+---------------+------+

Comparación de demostración dbfiddle.uk de 3 métodos

Mirando las estadísticas de io para la muestra:

Versión de pivote / pivote:

Table 'Worktable'. Scan count 0, logical reads 0
Table 'overrides'. Scan count 1, logical reads 1

Versión first_value over():

Table 'Worktable'. Scan count 20, logical reads 100
Table 'overrides'. Scan count 1, logical reads 1

Versión de subconsulta select top 1:

Table 'overrides'. Scan count 5, logical reads 5
Table 'Worktable'. Scan count 0, logical reads 0
4
SqlZim 26 oct. 2017 a las 18:04

Puedes usar first_value():

select distinct fkid,
       first_value(col1) over (partition by fkid
                               order by (case when col1 is not null then 1 else 2 end),
                                        when_inserted desc
                              ) as col1,
       first_value(col2) over (partition by fkid
                               order by (case when col2 is not null then 1 else 2 end),
                                        when_inserted desc
                              ) as col2,
       . . .
from t;

select distinct se debe a que SQL Server no tiene la funcionalidad equivalente como una función de agregación.

1
Gordon Linoff 26 oct. 2017 a las 17:41

Ver mi solución es bastante diferente.

IMHO, mi script performance será mejor siempre que dé correct output en todos los sample data.

He usado la identificación generada automáticamente en mi script, pero en caso de que no tenga una identificación de identidad, puede usar ROW_NUMBER. y mi guión es muy fácil de entender.

declare @t table(id int identity(1,1),FK_Id int,Col1 varchar(50),Col2 varchar(50)
,Col3 varchar(50),Col4 varchar(50),When_Inserted date)
insert into @t VALUES
 (1 ,'Val1_1'  ,'Val2_1' ,'Expected_Val3',  NULL ,  '2017-01-1')
,(1 ,NULL     ,'Val2_2' , NULL ,  NULL,       '2017-01-2')
,(1 ,NULL     ,'Expected_Val2', NULL ,         NULL, '2017-01-3')
,(1 ,'Expected_Val1' , NULL  , NULL ,         NULL,  '2017-01-4')



;

WITH CTE
AS (
    SELECT *
        ,CASE 
            WHEN col1 IS NULL
                THEN NULL
            ELSE CONCAT (
                    cast(id AS VARCHAR(10))
                    ,'_'
                    ,col1
                    )
            END col1Code
        ,CASE 
            WHEN col2 IS NULL
                THEN NULL
            ELSE CONCAT (
                    cast(id AS VARCHAR(10))
                    ,'_'
                    ,col2
                    )
            END col2Code
        ,CASE 
            WHEN col3 IS NULL
                THEN NULL
            ELSE CONCAT (
                    cast(id AS VARCHAR(10))
                    ,'_'
                    ,col3
                    )
            END col3Code
        ,CASE 
            WHEN col4 IS NULL
                THEN NULL
            ELSE CONCAT (
                    cast(id AS VARCHAR(10))
                    ,'_'
                    ,col4
                    )
            END col4Code
    FROM @t
    )
    ,CTE1
AS (
    SELECT FK_Id
        ,max(col1Code) col1Code
        ,max(col2Code) col2Code
        ,max(col3Code) col3Code
        ,max(col4Code) col4Code
    FROM cte
    GROUP BY FK_Id
    )
SELECT FK_Id
    ,SUBSTRING(col1Code, charindex('_', col1Code) + 1, len(col1Code)) col1Code
    ,SUBSTRING(col2Code, charindex('_', col2Code) + 1, len(col2Code)) col2Code
    ,SUBSTRING(col3Code, charindex('_', col3Code) + 1, len(col2Code)) col3Code
    ,SUBSTRING(col4Code, charindex('_', col4Code) + 1, len(col4Code)) col4Code
FROM cte1 c1
0
KumarHarsh 31 oct. 2017 a las 11:39