MySQL Select Where spliting results into different column

Question

I'm trying to split results into different column, I've been searching for that for few days but couldn't find the correct term of what I'd like to do. I'm probably using wrong key words.

I have a table like this:

Timestamp    System    Value
1             1          13
1             2          12
1             3          5
2             1          37
2             2          12
2             3          55
3             1          12
3             2          45
3             3          45

And I would like to get a result like that:

Timestamp, System1val, System2val, System3val
1             13            12        5
2             37            12        55
3             12            45        45

I'm pretty sure it's possible to do it. I've been close by using:

SELECT  
    FROM_UNIXTIME(timestamp) as time,  
    CASE System
        WHEN '1' THEN System
        END AS System1val,
    CASE System
        WHEN '2' THEN pyr.measure_conv
        END AS System2val,
    CASE System
        WHEN '3' THEN pyr.measure_conv
        END AS System3val
FROM

This actually return different fields and fill in with NULL value. The final goal would be to group by timestamp and have all three values

The keyword you are looking for is pivot.

user359040
– user359040

2013-06-05 08:44:52 +00:00
Commented Jun 5, 2013 at 8:44 — user359040
– user359040, Commented Jun 5, 2013 at 8:44

Steph Locke · Accepted Answer · 2013-06-05 09:01:25Z

4

This is a 'cross-tab' challenge. You need to structure stuff in this way...

SELECT
Timestamp,
SUM(CASE WHEN System=X THEN Value ELSE 0 END) AS X,
SUM(CASE WHEN System=Y THEN Value ELSE 0 END) AS Y
FROM System
GROUP BY Timestamp

edited Jun 5, 2013 at 9:01

answered Jun 5, 2013 at 8:39

Steph Locke

6,1964 gold badges44 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Steph Locke Over a year ago

@Damien It should be pretty efficient. When you don't have functions which do the behaviour for you like sql server's PIVOT then this is the 'design pattern' that gets followed as it's the easiest to understand and is compact - that being said it's not good for an unknown number of values you want transposed but that is a challenge I'd handle in the presentation layer.

Damien Over a year ago

I'm still having a problem, with this structure, each entry is returned three times and therefor the sum is returning a value three times higher. I could divide by three but it should be a cleaner way to go. the SUM block the use of groupby x an y which could have solved this issue.

Steph Locke Over a year ago

D'oh, forgot my group by for the column you are 'pivoting' the info on

Damien Over a year ago

Yes even with the group the values are returned three times, I'm quite surprised actually. If I group by timestamp, system1, system2, etc then it's fine, but this is not compatible with the SUM

peterm · Accepted Answer · 2013-06-05 08:46:17Z

2

Try

SELECT  
    timestamp time,  
    SUM(CASE WHEN system = 1 THEN value ELSE NULL END) System1val,
    SUM(CASE WHEN system = 2 THEN value ELSE NULL END) System2val,
    SUM(CASE WHEN system = 3 THEN value ELSE NULL END) System3val
FROM Table1
 GROUP BY timestamp

Output:

| TIME | SYSTEM1VAL | SYSTEM2VAL | SYSTEM3VAL |
-----------------------------------------------
|    1 |         13 |         12 |          5 |
|    2 |         37 |         12 |         55 |
|    3 |         12 |         45 |         45 |

Here is SQLFiddle demo

answered Jun 5, 2013 at 8:46

peterm

93k16 gold badges157 silver badges162 bronze badges

Collectives™ on Stack Overflow

MySQL Select Where spliting results into different column

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related