clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36310 in the last week).

CREATE TABLE test (group_id INT, a_id INT, b_id INT, c_id INT, d_id INT); INSERT INTO test VALUES (1, 1, null, null, null), (1, null, 2, null, null), (1, null, null, 3, null), (1, null, null, null, 4), (1, null, null, null, 5), (2, 11, null, null, null), (2, null, 12, null, null), (2, null, null, 13, null); SELECT * FROM test;
group_id a_id b_id c_id d_id
1 1
1 2
1 3
1 4
1 5
2 11
2 12
2 13
 hidden batch(es)


WITH cte_a AS ( SELECT group_id, a_id, ROW_NUMBER() OVER (PARTITION BY group_id) rn FROM test WHERE a_id IS NOT NULL ), cte_b AS ( SELECT group_id, b_id, ROW_NUMBER() OVER (PARTITION BY group_id) rn FROM test WHERE b_id IS NOT NULL ), cte_c AS ( SELECT group_id, c_id, ROW_NUMBER() OVER (PARTITION BY group_id) rn FROM test WHERE c_id IS NOT NULL ), cte_d AS ( SELECT group_id, d_id, ROW_NUMBER() OVER (PARTITION BY group_id) rn FROM test WHERE d_id IS NOT NULL ), cte_n AS ( SELECT group_id, rn FROM cte_a UNION SELECT group_id, rn FROM cte_b UNION SELECT group_id, rn FROM cte_c UNION SELECT group_id, rn FROM cte_d ) SELECT group_id, cte_a.a_id, cte_b.b_id, cte_c.c_id, cte_d.d_id FROM cte_n LEFT JOIN cte_a USING (group_id, rn) LEFT JOIN cte_b USING (group_id, rn) LEFT JOIN cte_c USING (group_id, rn) LEFT JOIN cte_d USING (group_id, rn) ORDER BY group_id, rn;
group_id a_id b_id c_id d_id
1 1 2 3 4
1 5
2 11 12 13
 hidden batch(es)