如何编写SQL查询以将行映射到列？

Question

考虑以下来自数据库的查询，

SELECT propertyname,propertyvalue,value, 
FROM db 

它会返回

| propertyname       | propertyvalue  | value    | 
+--------------------+----------------+----------+
| AnimalNumber       | 1              | 1.3      |   
| Group              | 1              | 1.3      |
| TimePoint          | 24 days        | 1.3      |
| Treatment method   | vehicle        | 1.3      |
| Treatment Conc     | 0              | 1.3      |
| AnimalNumber       | 2              | 0.5      |   
| Group              | 3              | 0.5      |
| TimePoint          | 7 days         | 0.5      |
| Treatment method   | vehicle        | 0.5      |
| Treatment Conc     | 0              | 0.5      |

我们可以看到，多行映射到相同的'value‘数据点。实际上，这5个属性(动物编号、组、时间点、治疗方法、治疗浓度)中的每一个都应该是列，并且前5个数据点应该用value=1.3压缩成一行。换句话说，它应该是这样的

| AnimalNumber | Group | TimePoint | Treatment method | Treatment Conc  | value
+--------------+-------+-----------+------------------+-----------------+------+
| 1            | 1     | 24 days   | vehicle          | 0               | 1.3  |          
| 2            | 3     |  7 days   | vehicle          | 0               | 0.5  |

还请注意，不一定存在1-1映射到值的情况，动物编号、组、时间点、治疗方法和治疗conc的多个组合可以映射到相同的值。因此，我不认为按值分组是正确的方法。还要注意，这里提供的所有数据当然都是伪造的，不是真实的。

Answer 1

一种方法是按值分组：

select max(propertyvalue) filter(where propertyname='AnimalNumber') AnimalNumber
      , max(propertyvalue) filter(where propertyname='TimePoint') TimePoint
      , max(propertyvalue) filter(where propertyname='Treatment method') "Treatment method"
      , max(propertyvalue) filter(where propertyname='Treatment Conc') "Treatment Conc"
      , max(propertyvalue) filter(where propertyname='Group') "Group"
from tablename
group by value