Omer Farooq Omer Farooq - 3 months ago 12
MySQL Question

SQL Query to pivot and combine rows

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

enter image description here

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

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.

Answer

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.