By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2017 (RTM-CU21) (KB4557397) - 14.0.3335.7 (X64) Jun 12 2020 20:39:00 Copyright (C) 2017 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |
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',
8 rows affected
select * from #data
| CustomerCode | Frequency | Type | TypeAmount |
|---|---|---|---|
| C12345 | Monthly | null | null |
| C12345 | null | A1 | 5.0000 |
| C12345 | null | A2 | 20.0000 |
| C12345 | Fornightly | null | null |
| C12345 | null | A1 | 5.0000 |
| C12345 | null | A2 | 20.0000 |
| C56789 | Fornightly | null | null |
| C56789 | null | A1 | 50.0000 |
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;
| customerCode | frequency | type | TypeAmount | rn |
|---|---|---|---|---|
| C12345 | Monthly | A1 | 5.0000 | 2 |
| C12345 | Monthly | A2 | 20.0000 | 3 |
| C12345 | Fornightly | A1 | 5.0000 | 5 |
| C12345 | Fornightly | A2 | 20.0000 | 6 |
| C56789 | Fornightly | A1 | 50.0000 | 2 |