I have the following results across a table, and what I need to get is the Frequency column down onto the appropriate rows, so the NULL values in the Frequency column are replaced with the appropriate value. Noting the CustomerCode different values.

Anyone know how this could be done, and in doing so removing the Frequency row that doesn't have the Type and TypeAmount?

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

Desired Output

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

Sample Data

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

CodePudding user response：

RECURSIVE CTE should do the trick:

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;

Result:

Query explanation:

1. Creating a rownumber so that we can refer to the previous row to get the frequency
2. 1st part of CTE will fetch NON NULL frequencies and 2nd will fetch NULL frequencies
3. Joining both with 1 row ahead to get the respective Frequency

See DEMO here