By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with t as (
select *
from (values (1, 100, '1-Purchase Order', 'Address', 'Cedar Rd'),
(2, NULL, NULL, 'City', 'Beachwood'),
(3, NULL, NULL, 'State', 'Ohio'),
(4, NULL, NULL, 'Zip', '44122'),
(5, 200, '1-Purchase Order', 'Address', 'Cedar Rd'),
(6, NULL, NULL, 'City', 'Beachwood'),
(7, NULL, NULL, 'State', 'Ohio'),
(8, NULL, NULL, 'Zip', '44123'),
(9, 300, '1-Purchase Order', 'Address', 'Cedar Rd'),
(10, NULL, NULL, 'City', 'Beachwood'),
(11, NULL, NULL, 'State', 'Ohio'),
(12, NULL, NULL, 'Zip', '44124')
) v(Rownumber, ID, Type, Category, Value)
)
select row_number() over (order by min(id)) as rownumber,
new_id as id,
max(type) as type,
max(case when category = 'Address' then value end) as address,
max(case when category = 'City' then value end) as city,
max(case when category = 'State' then value end) as state,
max(case when category = 'Zip' then value end) as zip
from (select t.*,
coalesce(id, max(id) over (order by rownumber)) as new_id
from t
) t
group by new_id;
| rownumber | id | type | address | city | state | zip |
|---|---|---|---|---|---|---|
| 1 | 100 | 1-Purchase Order | Cedar Rd | Beachwood | Ohio | 44122 |
| 2 | 200 | 1-Purchase Order | Cedar Rd | Beachwood | Ohio | 44123 |
| 3 | 300 | 1-Purchase Order | Cedar Rd | Beachwood | Ohio | 44124 |
Warning: Null value is eliminated by an aggregate or other SET operation.