Query to change vertical to horizontal

Question

I have a table Machine_Mode_Duration:

I need a query so that it will be displayed as follows:

Suggestions are appreciated!

Answer 1

You need a GROUP BY.

Assuming that you have exactly 3 modes and that in case of duplicate (Machine_id, INTERNAL_MODES) tuples it is okay to sum up their INTERNAL_MODE_DURATION:

SELECT
    Machine_Id,
    SUM(CASE WHEN INTERNAL_MODES = 1 THEN INTERNAL_MODE_DURATION ELSE 0 END) AS Mode_1,
    SUM(CASE WHEN INTERNAL_MODES = 2 THEN INTERNAL_MODE_DURATION ELSE 0 END) AS Mode_2,
    SUM(CASE WHEN INTERNAL_MODES = 3 THEN INTERNAL_MODE_DURATION ELSE 0 END) AS Mode_3
FROM t
GROUP BY
    Machine_Id;