Combine columns in result from SQL SELECT

Question

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

Joe Stefanelli · Accepted Answer · 2013-10-01 15:51:35Z

2

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 ...

answered Oct 1, 2013 at 15:51

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

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

1 Comment

Alex K. Over a year ago

IIRC If the 1st row resulted in an int and the 2nd in a char type you would need a cast to a type that supported both (eg char)

zloctb · Accepted Answer · 2013-10-01 15:51:24Z

2

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

answered Oct 1, 2013 at 15:51

zloctb

11.3k10 gold badges78 silver badges92 bronze badges

Collectives™ on Stack Overflow

Combine columns in result from SQL SELECT

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related