The first thing I advise is taking a look at what you actually want mapped. It doesn't make sense to me.

You want app3 stuff mapped to app4?

The second thing I recommend is giving us the DDL for the table, an INSERT statement with sample data, and then post a typed example (rather than an image) of what you want the results of the query to look like (because that's not the same thing as the excel sheet image you posted).

Then we can help you find your best options.

In the meantime, I can recommend that you look into Row_Number() and Common Table Expressions.