How to Create View based on Column values

Question

I have a table:

+------+----------+----------+-------+
| id   | location | variable | value |
+------+----------+----------+-------+
| 1    | loc1     | outside  | 30.6  |
| 2    | loc1     | inside   | 22.3  |
| 3    | loc2     | outside  | 31.1  |
| 4    | loc2     | inside   | 22.2  |
| 5    | loc3     | outside  | 34.7  |
| 6    | loc3     | inside   | 21.9  |
+------+----------+----------+-------+

Is it possible to create view that shows this in this way:

+----------+----------+-------+
| location | outside  | inside|
+----------+----------+-------+
| loc1     | 30.6     | 22.3  |
| loc2     | 31.1     | 22.2  |
| loc3     | 34.7     | 21.9  |
+----------+----------+-------+

I tried to Get DISTINCT values for each column "location" and "location" but don't know how to create a columns with these values populate them correctly.

Answer 1

You can use conditional aggregation (depending on the current model represents the whole dataset) like

SELECT location, 
       MAX(CASE WHEN variable='outside' THEN value END) AS outside,
       MAX(CASE WHEN variable='inside' THEN value END) AS inside
  FROM [tab]
 GROUP BY location 

or replace presumably MAX aggregation by SUM depending whether multiple rows exist for each location and variable combination.