ID Category Amount
1 A 120
2 B 100
Result
ID LineID Category Amount
1 1 A 60
1 2 A 60
2 1 B 100
Want to split the amount by 50% if category is A thus creating 2 records.
Union solution doesnot look optimal.
select col1, 1,col3, amount
from t
where col3='A'
union
select col1,1, col3, amount0.5 from
T
where col3='B'
union
select col1,2, col3, amount0.5 from
T
where col3='B'