CODEWITHSUNDEEP
mysqlsql
user2835433
user2835433

Reputation: 45

Combine columns in result from SQL SELECT

I am trying to solve following problem:

I have a table with timeStamp column and three columns for different types of variables. The columns with values are filled randomly according to the conditions. So it looks like this:


smpl_time  float_val  num_val  str_val
15:31         10            NULL      NULL
15:32         NULL       15.4        NULL
15:33         NULL        NULL      Disabled

What I would like to achieve is a result that would merge all val fields into one column and sorts them according to timeStamp:


smpl_time merge_val
15:31        10
15:32        15.4
15:33        Disabled



So far I have a SELECT just for one val type looking like this (it contains also other field from the table that are common for each entry): 

SELECT s.smpl_time AS Time,s.float_val AS Value, e.name AS Severity, m.name AS Status 
FROM sample s JOIN channel c ON c.channel_id=s.channel_id JOIN severity e ON
e.severity_id=s.severity_id JOIN status m ON m.status_id=s.status_id WHERE
s.channel_id='id' AND smpl_time BETWEEN TIMESTAMP 'startTime' AND TIMESTAMP 'stopTime';


Does anyone have an idea how to do this or if it is even possible?

Cheers,

Mike

Upvotes: 3

Views: 90

Answers (2)

Joe Stefanelli
Joe Stefanelli

Reputation: 135729

Assuming only one of the three columns is populated for any given row, use the COALESCE function.

SELECT smpl_time, COALESCE(float_vaL, num_val, str_val) AS merge_val
    FROM ...

Upvotes: 2

zloctb
zloctb

Reputation: 11177

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce

COALESCE(value,...)

Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

mysql> SELECT COALESCE(NULL,1); -> 1

mysql> SELECT COALESCE(NULL,NULL,NULL); -> NULL

Upvotes: 2

Related Questions