如何基于列值创建视图

Question

我有一个表格：

+------+----------+----------+-------+
| 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  |
+------+----------+----------+-------+

是否可以创建以这种方式显示此内容的视图：

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

我试图为每列获取不同的值"location" and "location"但不知道如何使用这些值创建列以正确填充它们。

Answer 1

您可以使用条件聚合(取决于当前模型表示整个数据集)，例如

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 

或者通过SUM替换大概MAX聚合，具体取决于每个location和variable组合是否存在多行。