CODEWITHSUNDEEP
mysqlsqldatabasedatetimepivot
MoBasheer
MoBasheer

Reputation: 5

How to get multiple columns from one column, SQL

I have been looking for the solution for a while, I have the following SQL database:

| Date                  | Name          | Value |
|-----------------------|---------------|-------|
|  01-01-2020:12:00     |  sensor01     |   5   |
|  01-01-2020:13:00     |  sensor01     |   15  |
|  01-01-2020:14:00     |  sensor01     |   25  |
|  01-01-2020:12:00     |  sensor02     |   10  |
|  01-01-2020:13:00     |  sensor02     |   20  |
|  01-01-2020:14:00     |  sensor02     |   30  |

What I want to do is to actually spread the data from the Name column in multiple columns where the Values are matched under each column. So as follow:

| Date                  | Sensor01      | Sensor02  |
|-----------------------|---------------|-----------|
|  01-01-2020:12:00     |  5            |   10      |
|  01-01-2020:13:00     |  15           |   20      |
|  01-01-2020:14:00     |  25           |   30      |

I tried several things including: the "PIVOT" function and "when case" but that didn't really help and I am still stuck! How can I best do that? which functions should be used?

I am still beginner, so any tips / ideas / advice are highly appreciated.

Thanks in advance!

Upvotes: 0

Views: 781

Answers (1)

GMB
GMB

Reputation: 222482

Use conditional aggregation:

select
    date,
    max(case when name = 'sensor01' then value end) sensor01,
    max(case when name = 'sensor02' then value end) sensor02
from mytable
group by date

Upvotes: 2

Related Questions