By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE myTable
([ForeignKey] varchar(2), [Product] varchar(7),
[Manufacturing] varchar(13), [Completed] datetime null)
;
INSERT INTO myTable
([ForeignKey], [Product], [Manufacturing], [Completed])
VALUES
('01', 'Shoes', 'Step A', '2020-02-24 00:00:00'),
('02', 'Shirt', 'Step A', '2020-02-25 00:00:00'),
('03', 'Pants', 'Step A', '2020-02-25 00:00:00'),
('01', 'Shoes', 'Step B', '2020-02-24 13:56:00'),
('02', 'Shirt', 'Step B', NULL),
('03', 'Pants', 'Step B', '2020-02-25 13:11:00'),
('04', 'Hat', 'Step B', NULL),
('04', 'Hat', 'Step A', NULL)
;
with pvt as (
select * from myTable
pivot (
max(completed) for Manufacturing in ([Step A], [Step B])
) tmp
)
select ForeignKey, Product,
case
when [Step A] is null and [Step B] is null then 'Step A'
else 'Step B' end as Manufacturing,
case
when [Step A] is not null or [Step B] is not null then [Step B] end as Completed
from pvt
order by foreignKey;
| ForeignKey | Product | Manufacturing | Completed |
|---|---|---|---|
| 01 | Shoes | Step B | 2020-02-24 13:56:00.000 |
| 02 | Shirt | Step B | null |
| 03 | Pants | Step B | 2020-02-25 13:11:00.000 |
| 04 | Hat | Step A | null |