By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @tbl TABLE (ID INT IDENTITY(1,1) PRIMARY KEY,
SalesOrder NVARCHAR(20),
SalesOrderLine INT,
MStockCode INT,
MPrice DECIMAL(18,2),
MBomFlag VARCHAR(1))
INSERT INTO @tbl (SalesOrder, SalesOrderLine, MStockCode, MPrice, MBomFlag) VALUES
('000000000182181', 1, '901337', 0.00000, 'P'),
('000000000182181', 2, '901335', 2476.90000, 'C'),
('000000000182181', 3, '340151', 0.00000, 'C'),
('000000000182181', 5, '900894', 0.00000, 'P'),
('000000000182181', 6, '400379', 0.00000, 'C'),
('000000000182181', 7, '900570', 600.90000, 'C');
SELECT
pv.SalesOrder,
SalesOrderLine = MIN(CASE WHEN pv.MBomFlag <> 'C' THEN pv.SalesOrderLine END),
MStockCode = MIN(CASE WHEN pv.MBomFlag <> 'C' THEN pv.MStockCode END),
MPrice = MIN(CASE WHEN pv.MBomFlag <> 'C' THEN pv.MPrice END),
MBomFlag = MIN(CASE WHEN pv.MBomFlag <> 'C' THEN pv.MBomFlag END)
FROM (
SELECT *,
GroupingId = COUNT(NULLIF(t.MBomFlag, 'C')) OVER (PARTITION BY t.SalesOrder ORDER BY t.SalesOrderLine ROWS UNBOUNDED PRECEDING)
FROM @tbl t
) pv
GROUP BY
pv.SalesOrder,
pv.GroupingId
| SalesOrder | SalesOrderLine | MStockCode | MPrice | MBomFlag |
|---|---|---|---|---|
| 000000000182181 | 1 | 901337 | 0.00 | P |
| 000000000182181 | 5 | 900894 | 0.00 | P |
Warning: Null value is eliminated by an aggregate or other SET operation.