By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH yourTable AS (
SELECT 'Order_1' AS Order_ID, 'OR_1' AS Report1_ID, NULL AS Report2_ID, NULL AS Report3_ID UNION ALL
SELECT 'Order_2', NULL, 'OR_2', NULL UNION ALL
SELECT 'Order_3', NULL, NULL, 'OR_3' UNION ALL
SELECT 'Order_4', 'OR_4', NULL, NULL UNION ALL
SELECT 'Order_5', NULL, 'OR_5', NULL UNION ALL
SELECT 'Order_6', NULL, NULL, 'OR_6' UNION ALL
SELECT 'Order_7', 'OR_7', NULL, NULL UNION ALL
SELECT 'Order_8', NULL, 'OR_8', NULL UNION ALL
SELECT 'Order_9', NULL, NULL, 'OR_9'
),
cte AS (
SELECT t.*, ROW_NUMBER() OVER (ORDER BY Order_ID) rn
FROM yourTable t
)
SELECT ROW_NUMBER() OVER (ORDER BY (rn - 1) / 3) AS Serial_NO,
MAX(Report1_ID) AS Report1_ID,
MAX(Report2_ID) AS Report2_ID,
MAX(Report3_ID) AS Report3_ID
FROM cte
GROUP BY (rn - 1) / 3
ORDER BY (rn - 1) / 3;
| Serial_NO | Report1_ID | Report2_ID | Report3_ID |
|---|---|---|---|
| 1 | OR_1 | OR_2 | OR_3 |
| 2 | OR_4 | OR_5 | OR_6 |
| 3 | OR_7 | OR_8 | OR_9 |
Warning: Null value is eliminated by an aggregate or other SET operation.