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

WITH test_data AS ( --PRODUCT | VALUE --:------ | ----: SELECT 125 AS ord, 'Ties' AS product, 'P' AS concept FROM dual UNION ALL SELECT 127 AS ord, 'tax', 'P' FROM dual UNION ALL SELECT 345 AS ord, 'Stocks', 'T' FROM dual UNION ALL SELECT 346 AS ord, 'tax', 'P' FROM dual UNION ALL SELECT 58 AS ord, 'Shirts', 'P' FROM dual UNION ALL SELECT 59 AS ord, 'tax', 'P' FROM dual UNION ALL SELECT 723 AS ord, 'Shirts', 'P' FROM dual UNION ALL SELECT 724 AS ord, 'tax', 'P' FROM dual UNION ALL SELECT 95 AS ord, 'Shirts', 'P' FROM dual UNION ALL SELECT 96 AS ord, 'tax', 'P' FROM dual UNION ALL SELECT 1102 AS ord, 'Stocks', 'T' FROM dual UNION ALL SELECT 1103 AS ord, 'tax', 'T' FROM dual UNION ALL SELECT 366 AS ord, 'Stocks', 'T' FROM dual UNION ALL SELECT 367 AS ord, 'tax', 'T' FROM dual UNION ALL SELECT 1555 AS ord, 'Pants', 'T' FROM dual UNION ALL SELECT 1556 AS ord, 'tax', 'T' FROM dual UNION ALL SELECT 1787 AS ord, 'Stocks', 'T' FROM dual UNION ALL SELECT 1788 AS ord, 'tax', 'T' FROM dual UNION ALL SELECT 197 AS ord, 'Shirts', 'P' FROM dual UNION ALL SELECT 198 AS ord, 'tax', 'P' FROM dual ), test_data_extended AS ( SELECT product, concept, LAG(product, 1) OVER (ORDER BY ord) AS pre_product,ord FROM test_data ), test_data_new AS ( SELECT product AS old_product, concept, ord, CASE WHEN product = 'tax' THEN 'tax (' || pre_product || ')' ELSE product END AS new_product, CASE WHEN product = 'tax' THEN 1 ELSE 0 END AS id_d FROM test_data_extended ), new_data AS ( SELECT UNIQUE ord, new_product, concept, id_d FROM test_data_new order by concept,id_d )SELECT * FROM new_data
ORD NEW_PRODUCT CONCEPT ID_D
58 Shirts P 0
95 Shirts P 0
125 Ties P 0
197 Shirts P 0
723 Shirts P 0
59 tax (Shirts) P 1
96 tax (Shirts) P 1
127 tax (Ties) P 1
198 tax (Shirts) P 1
346 tax (Stocks) P 1
724 tax (Shirts) P 1
345 Stocks T 0
366 Stocks T 0
1102 Stocks T 0
1555 Pants T 0
1787 Stocks T 0
367 tax (Stocks) T 1
1103 tax (Stocks) T 1
1556 tax (Pants) T 1
1788 tax (Stocks) T 1
 hidden batch(es)