FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

CREATE TABLE Operations (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Product INT,
    OP INT,
    Outcome INT
);
INSERT INTO Operations (Product, OP, Outcome) VALUES
(1, 1, 0),
(1, 1, 0),
(1, 1, 1),
(1, 2, 1),
(1, 3, 1),
(1, 4, 0),
(1, 4, 0),
(1, 4, 1),
(1, 5, 0),
(1, 5, 1),
(2, 1, 1),
(2, 2, 0),
(2, 2, 0),
(2, 2, 1),
(2, 3, 0),
(2, 3, 0),
(2, 3, 1),
(2, 4, 1),
(2, 5, 0),
(2, 5, 1);

SELECT * FROM Operations;

WITH cte AS (
    SELECT *,
        -count(CASE WHEN Outcome = 0 THEN 1 END) OVER (ORDER BY id DESC) AS grp
    FROM Operations
) SELECT DISTINCT
    -- grp,
    Product,
    max(CASE WHEN OP = 1 THEN Outcome END) OVER (PARTITION BY Product ORDER BY grp) AS OP1,
    max(CASE WHEN OP = 2 THEN Outcome END) OVER (PARTITION BY Product ORDER BY grp) AS OP2,
    max(CASE WHEN OP = 3 THEN Outcome END) OVER (PARTITION BY Product ORDER BY grp) AS OP3,
    max(CASE WHEN OP = 4 THEN Outcome END) OVER (PARTITION BY Product ORDER BY grp) AS OP4,
    max(CASE WHEN OP = 5 THEN Outcome END) OVER (PARTITION BY Product ORDER BY grp) AS OP5
FROM cte
GROUP BY
grp,
Product,
OP,
Outcome

ID Product OP Outcome
----------- ----------- ----------- -----------
1 1 1 0
2 1 1 0
3 1 1 1
4 1 2 1
5 1 3 1
6 1 4 0
7 1 4 0
8 1 4 1
9 1 5 0
10 1 5 1
11 2 1 1
12 2 2 0
13 2 2 0
14 2 2 1
15 2 3 0
16 2 3 0
17 2 3 1
18 2 4 1
19 2 5 0
20 2 5 1
Product OP1 OP2 OP3 OP4 OP5
----------- ----------- ----------- ----------- ----------- -----------
1 0 NULL NULL NULL NULL
1 1 1 1 0 NULL
1 1 1 1 1 0
1 1 1 1 1 1
2 1 0 NULL NULL NULL
2 1 1 0 NULL NULL
2 1 1 1 1 0
2 1 1 1 1 1