CODEWITHSUNDEEP
mysqlsqlmariadb
Bruno Andrade
Bruno Andrade

Reputation: 595

Merge rows with different values into a single row

Query that I'm using

SELECT v.*,  if( up.upload_key = 'send', up.upload_value, 0 ) AS send,  
    if( up.upload_key = 'host', up.upload_value, 0 ) AS host, 
     if( up.upload_key = 'upload_id', up.upload_value, 0 ) AS upload_id 
FROM TableInit v JOIN TableName up ON up.video_id =  v.id_video;

The return of the query is this

| id_video | titulo | desc | send | host |    upload_id |
|----------|--------|------|------|------|--------------|
|        6 |  Title | Desc |    0 |  cnn |            0 |
|        6 |  Title | Desc |    0 |    0 |            0 |
|        6 |  Title | Desc |    0 |    0 | sHGN-tSNvJYs |

The return I need is this:

| id_video | titulo | desc | send | host |   upload_id |
|----------|--------|------|------|------|-------------|
|        6 |  Title | Desc |    0 |  cnn |sHGN-tSNvJYs |

If I use GROUP BY I have this return which is only the value of the first row and not the other

| id_video | titulo | desc | send | host | upload_id |
|----------|--------|------|------|------|-----------|
|        6 |  Title | Desc |    0 |  cnn |         0 |

SQLFiddle

Upvotes: 1

Views: 44

Answers (1)

hsusanoo
hsusanoo

Reputation: 914

This should do the job :

SELECT v.*,  if( up.upload_key = 'send', up.upload_value, 0 ) AS send,  
    MAX(if( up.upload_key = 'host', up.upload_value, null )) AS host, 
     MAX(if( up.upload_key = 'upload_id', up.upload_value, null )) AS upload_id 
FROM TableInit v JOIN TableName up ON up.video_id =  v.id_video GROUP BY id_video;

I just added MAX function to select only the max value of the columns host and upload_id.

The returned result :

+----------+--------+------+------+------+--------------+
| id_video | titulo | desc | send | host | upload_id    |
+----------+--------+------+------+------+--------------+
|        6 | Title  | Desc | 0    | cnn  | sHGN-tSNvJYs |
+----------+--------+------+------+------+--------------+

Upvotes: 1

Related Questions