Valores en diferentes filas: combínelos en 1 fila

Valores en diferentes filas: combínelos en 1 fila

Tengo los siguientes resultados en una tabla, y lo que necesito obtener es la columna Frecuencia hacia abajo en las filas correspondientes, por lo que los valores NULL en la columna Frecuencia se reemplazan con el valor apropiado. Observando los diferentes valores de CustomerCode.

¿Alguien sabe cómo se podría hacer esto y, al hacerlo, eliminar la fila de frecuencia que no tiene el tipo y la cantidad de tipo?

CustomerCode    Frequency   Type    TypeAmount
C12345          Monthly     NULL    NULL
C12345          NULL        A1      5.00
C12345          NULL        A2      20.00
C12345          Fortnightly NULL    NULL
C12345          NULL        A1      5.00
C12345          NULL        A2      20.00
C56789          Fortnightly NULL    NULL
C56789          NULL        A1      50.00

Salida deseada

CustomerCode    Frequency   Type    TypeAmount
C12345          Monthly     A1      5.00
C12345          Monthly     A2      20.00
C12345          Fortnightly A1      5.00
C12345          Fortnightly A2      20.00
C56789          Fortnightly A1      50.00

Datos de muestra

Create Table #Data
(
    CustomerCode varchar(50),
    Frequency varchar(50) NULL,
    Type varchar(50) NULL,
    TypeAmount money NULL
)

insert into #Data
(
    CustomerCode,
    Frequency,
    Type,
    TypeAmount 
)
select
    'C12345',
    'Monthly',
    NULL,
    NULL
union all
select
    'C12345',
    NULL,
    'A1',
    '5.00'
union all
select
    'C12345',
    NULL,
    'A2',
    '20.00'
union all
select
    'C12345',
    'Fornightly',
    NULL,
    NULL
union all
select
    'C12345',
    NULL,
    'A1',
    '5.00'
union all
select
    'C12345',
    NULL,
    'A2',
    '20.00'
union all
select
    'C56789',
    'Fornightly',
    NULL,
    NULL
union all
select
    'C56789',
    NULL,
    'A1',
    '50.00'

select * from #Data
Mostrar la mejor respuesta

La salida deseada sería útil.

@Arun - claro, hecho

He publicado una respuesta. ¡Échale un vistazo!

¿Esto funciona?

Gracias @arun: estoy probando en este momento, todavía se está ejecutando después de 8 minutos con los datos reales.

Sin otra columna para ordenar, ¿cómo sabe qué está asociado con qué? Este es un problema estándar de lagunas e islas, pero no se puede resolver sin un ordenamiento definitivo.

@Charlieface: los datos siempre están en el orden de la Frecuencia que se muestra primero, y luego las filas relacionadas debajo. ¿Algo que puedas proponer?

Lo siento, no es un pedido real: tienes Monthly NULL NULL Fortnightly NULL NULL Así que los nulos no están agrupados juntos. No existe tal cosa en SQL como "debajo" (las tablas no tienen un orden inherente), necesita un valor de columna real para ordenar. ¿Hay alguna columna como Id o CreatedDate que pueda usar?

@Charlieface: desafortunadamente no hay una identificación o fecha de creación.

Entonces es imposible. Como señalé en la respuesta a continuación, si no tiene un orden inherente, el resultado es arbitrario y puede obtener resultados diferentes cada vez. El hecho de que ejecutó el código incluso mil veces y obtuvo el mismo resultado no significa que no vaya a cambiar en el futuro. Las tablas SQL no tienen un orden inherente, no puede confiar en que regresen en un orden particular. No necesariamente regresan en el orden en que los insertas. Necesitas una clave principal o algún tipo de columna de orden

Bien, gracias @Charlieface. Si de alguna manera pudiera obtener algún tipo de columna de orden, supongo que podría cambiar la declaración Ordenar por en consecuencia en la respuesta a continuación.

Si, aunque hay mejores respuestas que esta.

Gracias @Charlieface: lo que podría hacer entonces es intentar incluir una identificación y crear una nueva pregunta aquí.

RECURSIVE CTE debería funcionar:

With cte AS
(
SELECT customerCode, frequency, type, TypeAmount, rn
FROM 
    (
     SELECT *, ROW_NUMBER()OVER(PARTITION BY CustomerCode ORDER BY CustomerCode) AS rn
     FROM #data
    ) AS d
WHERE Frequency IS NOT NULL

UNION ALL

SELECT d2.customerCode, cte.frequency, d2.type, d2.TypeAmount, d2.rn
From 
     (
      SELECT *, ROW_NUMBER()OVER(PARTITION BY CustomerCode ORDER BY CustomerCode) AS rn
      FROM #data
     ) AS d2
INNER JOIN cte
  ON d2.rn=cte.rn+1
  AND d2.CustomerCode=cte.CustomerCode
WHERE d2.Frequency IS NULL
)

SELECT * 
FROM cte 
WHERE Type IS NOT NULL 
  AND TypeAmount IS NOT NULL
ORDER BY CustomerCode, rn;

Resultado:

códigocliente frecuencia tipo TipoCantidad rn C12345 Mensual A1 5,0000 2 C12345 Mensual A2 20.0000 3 C12345 Quincenal A1 5,0000 5 C12345 Quincenal A2 20.0000 6 C56789 Quincenal A1 50,0000 2

Explicación de consulta:

  1. Crear un rownumber para que podamos consultar la fila anterior para obtener el frequency
  2. La primera parte de CTE obtendrá frecuencias NON NULL y la segunda parte obtendrá frecuencias NULL
  3. Unir ambos con 1 fila adelante para obtener el respectivo Frequency

Ver DEMO aquí

Gracias Arun, parece producir el resultado correcto. Sin embargo, es muy lento cuando se ejecuta contra decenas de miles de registros. ¿Algún cambio que puedas sugerir?

@Philip, supongo que esto no se puede hacer más rápido porque hemos usado JOINS en lugares posibles. Sin embargo, la creación de ROW_NUMBER podría realizarse antes de comenzar con CTE (para que la subconsulta se reduzca)

PARTITION BY CustomerCode ORDER BY CustomerCode es completamente no determinista y puede devolver resultados diferentes cada vez. El hecho de que haya funcionado una o cien veces no significa que siempre funcionará. necesita otra columna para ordenar por

Charlieface tiene razón, es necesario definir un orden en los datos. @Arun no hay necesidad de recursividad, presenta una complejidad innecesaria y desafíos de rendimiento. Solo necesita una APLICACIÓN CRUZADA simple para buscar el último valor de frecuencia poblado (vea mi respuesta)

;with cte1 as
(select CustomerCode,Frequency,ROW_NUMBER()over( order by 
customercode)rn,Type,TypeAmount from #Data a ),
cte2 as (   select * from cte1 where  Frequency is not null)
select row_number()over(order by 
cte2.rn)sno,cte1.CustomerCode,cte2.Frequency,cte1.Type,cte1.TypeAmount from cte1
inner join cte2 on cte1.CustomerCode=cte2.CustomerCode
where cte1.Type is not null
group by cte1.CustomerCode,cte2.Frequency,cte1.Type,cte1.TypeAmount,cte2.rn
order by 1

Debe haber algún orden definido para sus datos o de lo contrario no podrá realizar esta consulta. Creé un pedido insertando sus datos en una tabla temporal con una columna de identidad para su referencia. Supongo que hay algún orden subyacente definido en sus datos de origen. Simplemente intercambie eso con mi columna de clave sustituta [ID]

DROP TABLE IF EXISTS #Data

Create Table #Data
(
    ID int Identity(1,1),
    CustomerCode varchar(50),
    Frequency varchar(50) NULL,
    Type varchar(50) NULL,
    TypeAmount money NULL
)

INSERT INTO #Data (CustomerCode,Frequency,[Type],TypeAmount )
VALUES ('C12345','Monthly',NULL,NULL)
,('C12345',NULL,'A1','5.00')
,('C12345',NULL,'A2','20.00')
,('C12345','Fornightly',NULL,NULL)
,('C12345',NULL,'A1','5.00')
,('C12345',NULL,'A2','20.00')
,('C56789','Fornightly',NULL,NULL)
,('C56789',NULL,'A1','50.00')

Select *
FROM #Data

SELECT A.ID
    ,B.Frequency
    ,A.Type
    ,A.TypeAmount
from #Data as A
Cross Apply 
(   /*Grab most recent preceding row that has frequency populated*/
    SELECT Top (1) DTA.Frequency
    From #Data AS DTA
    Where A.CustomerCode = DTA.CustomerCode
    AND DTA.ID < A.ID
    AND DTA.Frequency IS NOT NULL
    Order by DTA.ID DESC
) AS B
WHERE A.Frequency IS NULL

Si el rendimiento es un problema, recomendamos crear un índice como este antes de ejecutar su selección:

Create Index ix on #Data(CustomerCode,ID) Include (Frequency)