You're asking about an ETL process which accepts data from new tables without having to be adjusted? In principal, sure, all things are possible. In practice, writing this would be a challenge.
There are two main approaches to combining data from two or more tables: joins and unions. These correspond to your two examples, where A, B, and C become one row (they're being joined) or three rows (they're being unioned).
Either way, your ETL process would need to have some kind of pattern with which to identify the sources. For example, it could look for tables named "Orders" and a number. You could write code to create dynamic SQL based on such a pattern, but having multiple nearly-identical tables differing only by suffix is generally a bad design. There are some similar concepts, such as partitioning.
Alternatively, if your tables are substantially different, you're essentially talking about writing an artificial intelligence to recognize and combine related data. This would be a huge project; suitable for a thesis, or DARPA maybe, but probably not a business with deadlines to meet. Any solution it created would need to be vetted and tweaked by hand to be accurate, so why not just update the code yourself?
Is there a business goal you're trying to meet, or is this a matter of curiosity?