Formatting SQL table data by creating a new column

Hi All,

I am not able to figure it out how I can solve issue by writing sql query.In below screen shot yellow columns are the source and green colour are output columns. How can I write a sql query to solve the issue? Appreciate any help from any one :)

---

Maruthi...