sql 查询以从行中的值插入到列中

Question

我有下表作为查询结果说 calc_success_rate

server,      service,     timestamp,     success_rate
123.23.23.2  ftp          1:00 am        1
123.23.23.2  http         1:00 am        0.5

我想将这些值插入到另一个表中，比如其结构如下的指标

server       ftpSuccessRate     httpsuccessrate       timestamp  
123.23.23.2  1                  0.5                   1:00

我将在 calc_success_rate.server 和 metrics.server 以及 calc_success_rate.timestamp 和 metrics.timestamp< 之间进行连接 并插入 ftpsuccessrate 和 httpsuccessrate

是否可以在一次查询中完成此操作

我需要这样的更新语句

update secondTable st 
set ftpSuccessRate = , httpSuccessRate = 
from firstTable ft 
where ft.server = st.server and ft.timestamp = st.timestamp

Answer 1

INSERT INTO SecondTable
    (server, ftpSuccessRate, httpSuccessRate, timestamp)
    SELECT server, 
           MAX(CASE WHEN service = 'ftp' THEN success_rate ELSE NULL END),
           MAX(CASE WHEN service = 'http' THEN success_rate ELSE NULL END), 
           timestamp
         FROM FirstTable
         GROUP BY server, timestamp;  

编辑:根据评论，这是更新版本:

UPDATE st 
    SET ftpSuccessRate = q.ftpSuccessRate,
        httpSuccessRate = q.httpSuccessRate
    FROM (SELECT server,  
                 MAX(CASE WHEN service = 'ftp' THEN success_rate ELSE NULL END) as ftpSuccessRate, 
                 MAX(CASE WHEN service = 'http' THEN success_rate ELSE NULL END) as httpSuccessRate,  
                 timestamp 
              FROM FirstTable 
              GROUP BY server, timestamp) q 
        INNER JOIN @SecondTable st 
            ON q.server = st.server 
                AND q.timestamp = st.timestamp