CODEWITHSUNDEEP
sqlsql-serverdatabasepivot
Sadiq
Sadiq

Reputation: 838

Query to change vertical to horizontal

I have a table Machine_Mode_Duration:

enter image description here

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

enter image description here

Suggestions are appreciated!

Upvotes: 5

Views: 15477

Answers (3)

Reza
Reza

Reputation: 19863

you can use PIVOT like this

SELECT Machine_ID, 
[1] as Mode_1, [2] as Mode_2, [3] as Mode_3 FROM
(SELECT Machine_ID, Internal_Mode_Duration , InternalModes
    FROM Machine_Mode_Duration) AS SourceTable
PIVOT
(
Sum(Internal_Mode_Duration)
FOR InternalModes IN ([1], [2], [3])
) AS PivotTable;

also you can use old fashioned method via CASE like the time that there were no PIVOT command ;)

Upvotes: 5

kudra
kudra

Reputation: 134

try this

select Machine_Id, 
       case when INTERNAL_MODES = 1 then INTERNAL_MODE_DURATION end as Mode_1,
       case when INTERNAL_MODES = 2 then INTERNAL_MODE_DURATION end as Mode_2,
       case when INTERNAL_MODES = 3 then INTERNAL_MODE_DURATION end as Mode_3
from Machine_Mode_Duration
group by Machine_Id
order by Machine_Id;

Upvotes: 1

Daniel Sparing
Daniel Sparing

Reputation: 2173

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;

Upvotes: 4

Related Questions