Combine multiple rows to one row

Question

I have a table with 3 columns:

COLUMN A: DATETIME
COLUMN B: DOUBLE
COLUMN C: INT

Column A is not unique there are many rows with the same DATETIME value, however for every DATETIME COLUMN C is unique.

Background: Somebody measured on 5 different positions a temperature and this is stored in the database as:

COLUMN A | COLUMN B | COLUMN C

12:00    |   23.4   |  4

12:00    |   24.4   |  5

12:00    |   25.4   |  6

12:01    |   26.4   |  4

12:01    |   27.4   |  5

12:01    |   28.4   |  6

I would like to transform this (in a new table or view) into something like:

COLUMN AA | COLUMNC_VALUE-4 | COLUMNC_VALUE-5 | COLUMNC_VALUE 6

12:00     |    23.4         |       24.4      |      25.4

12:01     |    26.4         |       27.4      |      28.4

The values in Column C can be found in another table.

Is this possible in SQL or do I have to write some code?

Answer 1

You want to transpose data but since MySQL does not have a builtin PIVOT() function, you can manually do it using MAX() and CASE.

SELECT  ColA,
        MAX(CASE WHEN ColC = 4 THEN ColB END) ColC_V4,
        MAX(CASE WHEN ColC = 5 THEN ColB END) ColC_V5,
        MAX(CASE WHEN ColC = 6 THEN ColB END) ColC_V6
FROM    TableName
GROUP   BY ColA

• SQLFiddle Demo

However, if you have unknown number of values in ColC, a much better way is to do in dynamically,

SET @sql = NULL;

SELECT  GROUP_CONCAT(DISTINCT
        CONCAT('MAX(CASE WHEN ColC = ''',
               ColC,
               ''' THEN ColB ELSE NULL END) AS ',
               CONCAT('`ColC_V', ColC, '`')
               )) INTO @sql
FROM TableName;

SET @sql = CONCAT('SELECT ColA, ', @sql, ' 
                   FROM TableName
                   GROUP BY ColA');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

• SQLFiddle Demo

Answer 2

It is a common problem named pivot table. And my opinion is - you should do that in application, not in SQL - that is because it's unreliable to act such way in DBMS. Imagine you will have 100 different values for columnC - what then? Or different count of columnC values per one datetime.

But if you still want to do that in MySQL, here is a good answer for this issue.