CODEWITHSUNDEEP
mysqlsql
michael
michael

Reputation: 57

Multiple SQL rows merge into single row if the id is same

What is the mysql I need to achieve the result below given this table:

table:

+----+-------+--------------
| name| id   |  items | vol
+----+-------+---------------
|  A | 1111  |  4     |  170
|  A | 1111  |  5     |  100
|  B | 2222  |  6     |  200
|  B | 2222  |  7     |  120
+----+-------+-----------------

Above table is the result of union query

SELECT * FROM imports
union all
SELECT * FROM exports
ORDER BY name;

I want to create a temporary view that looks like this

desired result:

+----+---------+---------+-------------------
| name| id     | items |  vol | items1 | vol2
+-----+--------+-------+--------------------
|  A  | 1111   |   4   |  170 |   5    | 100
|  B  | 2222   |   6   |  200 |   7    | 120
+----+---------+---------+-------------------

any help would be greatly appreciated! -Thanks

Upvotes: 2

Views: 4588

Answers (2)

krokodilko
krokodilko

Reputation: 36107

Use PIVOT:

SELECT name,id,
       SUM( CASE WHEN typ = 'imports' THEN items ELSE 0 END) as imports_items,
       SUM( CASE WHEN typ = 'imports' THEN vol   ELSE 0 END) as imports_vol,
       SUM( CASE WHEN typ = 'exports' THEN items ELSE 0 END) as exports_items,
       SUM( CASE WHEN typ = 'exports' THEN vol   ELSE 0 END) as exports_vol
FROM (
   SELECT 'imports' as typ, t.* FROM imports t
   union all
   SELECT 'exports' as typ, t.* FROM exports t
) x
GROUP BY name,id
ORDER BY name;

Upvotes: 1

Rusan Constantin
Rusan Constantin

Reputation: 124

This should give you the table you are looking for:

    SELECT 
        a.name, 
        a.id, 
        a.items, 
        a.vol, 
        b.items as items2, 
        b.vol as vol2
    FROM imports a 
        INNER JOIN exports b on b.id = a.id;

Upvotes: 0

Related Questions