CODEWITHSUNDEEP
sqlpivot
wardo
wardo

Reputation: 1

SQL: Separate different items in a column to make unique columns

I am no SQL expert and am a little stuck. I have a database table that looks like :

Date        | Equipment | Downtime
------------+-----------+---------
2018-10-03  | PLC       |   0
2018-10-03  | Robo      |   0
2018-10-04  | PLC       | 108
2018-10-04  | Robo      |   0

For a system I am making I need to make a chart using this table but I need to separate the equipment column to be different columns like this: 

Date        | Robo(Time)  | PLC(Time)
------------+-------------+----------
2018-10-03  |  0          | 0
2018-10-04  |  108        | 0

So I am storing the data in the third row in a column named after the corresponding contents in the second row. Does anyone know how this can be done? Thank you for your help!

Upvotes: 0

Views: 35

Answers (2)

Mike Baron
Mike Baron

Reputation: 848

Another way to do it is to join the table on itself over date:

select PLC.date,
    Robo.Downtime as [Robo(Time)],
    PLC.Downtime as [PLC(Time)]
FROM table as Robo INNER JOIN table as PLC ON Robo.Date = PLC.Date 
    AND Robo.Equipment = 'Robo' AND PLC.Equipment = 'PLC'

Note I fully qualified PLC.date, but you can qualify either. You just have to pick one because since you are joining itself, it's ambiguous otherwise.

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can do conditional aggregation :

select date, 
       max(case when Equipment = 'PLC' then Downtime end) as PLC,
       max(case when Equipment = 'Robo' then Downtime end) as Robo
from table t
group by date;

Upvotes: 1

Related Questions