Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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