By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE YourTable (
ID INT PRIMARY KEY,
A INT,
B INT,
in1 VARCHAR(255),
in2 DECIMAL(10, 2),
X DECIMAL(10, 2),
Y DECIMAL(10, 2)
);
INSERT INTO YourTable (ID, A, B, in1, in2, X, Y) VALUES
(1, 1, 11, 'chocolate', 1000.00, 1.00, 41.01),
(2, 1, 11, 'chocolate', 1000.00, 1.50, 41.28),
(3, 1, 11, 'chocolate', 1000.00, 2.00, 71.27),
(4, 1, 12, 'chocolate', 1000.00, 1.00, 32.62),
(5, 1, 12, 'chocolate', 1000.00, 1.50, 63.32),
(6, 1, 12, 'chocolate', 1000.00, 2.00, 20.76),
(7, 2, 11, 'chocolate', 1000.00, 1.00, 18.60),
(8, 2, 11, 'chocolate', 1000.00, 1.50, 58.34),
(9, 2, 11, 'chocolate', 1000.00, 2.00, 8.07),
(10, 2, 12, 'chocolate', 1000.00, 1.00, 45.80),
(11, 2, 12, 'chocolate', 1000.00, 1.50, 90.57),
(12, 2, 12, 'chocolate', 1000.00, 2.00, 26.14);
Records: 12 Duplicates: 0 Warnings: 0
SELECT
X
, CASE WHEN A = 1 AND B = 11 THEN Y END AS Y1
, CASE WHEN A = 2 AND B = 12 THEN Y END AS Y2
FROM YourTable
WHERE in1 = 'chocolate'
AND ABS(in2 - 1000.0) < 0.01
ORDER BY ID
| X | Y1 | Y2 |
|---|---|---|
| 1.00 | 41.01 | null |
| 1.50 | 41.28 | null |
| 2.00 | 71.27 | null |
| 1.00 | null | null |
| 1.50 | null | null |
| 2.00 | null | null |
| 1.00 | null | null |
| 1.50 | null | null |
| 2.00 | null | null |
| 1.00 | null | 45.80 |
| 1.50 | null | 90.57 |
| 2.00 | null | 26.14 |
SELECT
X
, MAX(CASE WHEN A = 1 AND B = 11 THEN Y END) AS Y1
, MAX(CASE WHEN A = 2 AND B = 12 THEN Y END) AS Y2
FROM YourTable
WHERE in1 = 'chocolate'
AND ABS(in2 - 1000.0) < 0.01
GROUP BY
X
ORDER BY
X
| X | Y1 | Y2 |
|---|---|---|
| 1.00 | 41.01 | 45.80 |
| 1.50 | 41.28 | 90.57 |
| 2.00 | 71.27 | 26.14 |
SELECT
X
, SUM(CASE WHEN A = 1
AND B = 11
AND in1 = 'chocolate'
AND ABS(in2 - 1000.0) < 0.01 THEN Y ELSE 0 END) AS Y1
, SUM(CASE WHEN A = 2
AND B = 12
AND in1 = 'chocolate'
AND ABS(in2 - 1000.0) < 0.01 THEN Y ELSE 0 END) AS Y2
FROM YourTable
WHERE (
A = 1
AND B = 11
AND in1 = 'chocolate'
AND ABS(in2 - 1000.0) < 0.01
)
OR (
A = 2
AND B = 12
AND in1 = 'chocolate'
AND ABS(in2 - 1000.0) < 0.01
)
GROUP BY
X
ORDER BY
X
| X | Y1 | Y2 |
|---|---|---|
| 1.00 | 41.01 | 45.80 |
| 1.50 | 41.28 | 90.57 |
| 2.00 | 71.27 | 26.14 |