SQL查询以透视和合并行

I have a table from which I need to derive results as shown below.

我有一个表，从中我需要得到如下所示的结果。

so far I am able to pivot F1 and combine currentvalue & mismatchcount using below sql.

到目前为止，我能够透视F1，并使用下面的sql合并currentvalue & mismatchcount。

SELECT object, GROUP_CONCAT(currentvalue  ,"-", mismatchcount ) as F1
FROM tableT  where freq = F1
GROUP BY object

but I need all three freq beside each other as output.

但是我需要三个freq放在一起作为输出。

1 个解决方案

#1

You could use this query:

您可以使用这个查询:

SELECT   object, 
         GROUP_CONCAT(CASE freq WHEN 'F1' THEN
                         CONCAT(currentvalue, "-", mismatchcount) END SEPARATOR " ") as F1,
         GROUP_CONCAT(CASE freq WHEN 'F2' THEN
                         CONCAT(currentvalue, "-", mismatchcount) END SEPARATOR " ") as F2,
         GROUP_CONCAT(CASE freq WHEN 'F3' THEN
                         CONCAT(currentvalue, "-", mismatchcount) END SEPARATOR " ") as F3
FROM     tableT 
GROUP BY object

The CONCAT function is used to concatenate values from the same record, GROUP_CONCAT is used to concatenate such values from different records. The CASE WHEN expression is evaluated for several records (with different values for freq) and thus returns several values. But of those values the non-null values need to concatenated. The separator is set to the space.

CONCAT函数用于连接来自同一记录的值，GROUP_CONCAT用于连接来自不同记录的这些值。当表达式为多个记录(对于freq有不同的值)求值，从而返回多个值时的情况。但在这些值中，非空值需要连接。分隔符被设置为空格。