SQL 选择语句(如果列与值匹配)作为列名

Question

示例数据：

表：轮询设置

示例行：

ID | Frequency      | Value
---+----------------+-------
1 | Frequency      | 300
1 | Timeout        | 500
2 | Frequency      | 1200
2 | Timeout        | 100
3 | LogFilesToKeep | 30
3 | DebugEnabled   | False

我需要一个选择语句来做一些事情：

SELECT ID, key as Frequency, key as Timeout
CASE Freqency
WHEN Frequency = 'Frequency' THEN value
CASE Timeout
WHEN Timeout = 'Timeout' THEN value
END
FROM PollingSettings

本质上，我需要创建两个名为超时和频率的新列，但前提是 ID 具有值为"频率"或"超时"的键列，并使用键值对中的值调用该列"频率"或"超时"。否则，仍应创建这些列，它们应仅设置为"null"。最终结果是这样的：

ID | Frequency   | Timeout
---+-------------+-------
1 | 300         | 500
2 | 1200        | 100

Answer 1

您只需要创建两个具有 case 语句的列，并获取最大值，以便每个 ID 只获得 1 行：

select ID, 
max(case when key='Frequency' then value end) as Frequency,
max(case when key='Timeout' then value end) as Timeout
from PollingSettings
group by ID

Answer 2

在以下情况下尝试此操作：

SELECT ID, min(CASE WHEN key = 'Frequency' THEN value end) as Frequency,
min(CASE WHEN key = 'Timeout' THEN value end) as Timeout
FROM PollingSettings group by ID