I have 4 diff tables as below:

Table columns are as below:

Need to select all columns from all the 4 tables.
I am able to select AMOUNT and RETAIL columns, but not able to figure out the way to select CA_XXXX_CA_APPLE_ID column.

Code tried so far:

 Select ' Select AMOUT,RETAIL'+
           ' from '+TABLE_SCHEMA+'.'+ table_name +
         ' Union All'
From INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE  'CA_%_APPLE';

It gives me below:

 Select AMOUT,RETAIL from data.CA_2003_CA_APPLE Union All
 Select AMOUT,RETAIL from data.CA_2004_CA_APPLE Union All
 Select AMOUT,RETAIL from data.CA_2005_CA_APPLE Union All
 Select AMOUT,RETAIL from data.CA_2006_CA_APPLE Union All

Desired Output:

 Select CA_2003_CA_APPLE_ID,AMOUT,RETAIL from data.CA_2003_CA_APPLE Union All
 Select CA_2004_CA_APPLE_ID,AMOUT,RETAIL from data.CA_2004_CA_APPLE Union All
 Select CA_2005_CA_APPLE_ID,AMOUT,RETAIL from data.CA_2005_CA_APPLE Union All
 Select CA_2006_CA_APPLE_ID,AMOUT,RETAIL from data.CA_2006_CA_APPLE Union All

is it even possible what I am trying to achieve?

Thanks