>> I want the result as below
1 ABC AMEA MI-1
2 DEF AMEA MI-2
3 XYZ APAC TX
since the count of region_code is 2 <<
No. You just changed the Postal Service state codes! You do not have that authority. Trust me This is also a violation of First Normal Form (1NF); the column has two totally difference vales shoved into it.
CREATE VIEW Sequenced_Foobars
SELECT foo_id, employee_name, region_code, state_code
OVER (PARTITION BY region_code, state_code
ORDER BY employee_name)
This view will always be correct, it is valid RDBMS and not require constantly updating that base table we had to guess at. Get qa copy of MANGA GUIDE TO DATABASE; it is a good guide to basics.