Another tip that I’m putting up here so that I can find it later myself.
Scenario: You’ve got a PowerApp that’s displaying data from a SQL database and you want to display information from multiple tables in a single gallery.
Issue: Each table has a different number of columns and matching columns have different names in different tables. This makes adding all the tables to a single collection infeasible (and would be too resource intensive anyway).
Solution: Create a view in SQL that joins the disparate tables while dealing with the different column names at the same time.
CREATE VIEW <ViewName> AS
SELECT <Column1> as <Column1>, <Column2> as <Column2>, 'Source1' as "DataSource"
FROM <Table1>
WHERE <Condition>;
UNION
SELECT <Column3> as <Column1>, <Column4> as <Column2>, 'Source2' as "DataSource"
FROM <Table2>
WHERE <Condition>;
- Using “as” in the SELECT statement enables us to name the final column that will appear in the view
- We can create and populate new columns (like DataSource) by using inverted commas
- The UNION statement will add the second table into the same view, using the column headings we define with the “as”
- Any number of UNION statments can be used
- We can use the WHERE statement to filter the records returned to the view
By using SQL views, we can cut down the amount of work that has to be done at run time in the app.
The Odd Blog 