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

Question

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

It's not great to say "here is some data that was prepared by a UNION query, how can I make it look like ... " because we cannot know for certain how the source data looked and we need to know because it affects how we answer. It is like saying "if the answer is 4, what is the question?" - could be 1+3, 2+2, 5-1 etc... Please split your question up to show the data in imports and the data in exports SEPARATELY — Caius Jard
– Caius Jard, Commented Jul 24, 2017 at 20:43

krokodilko · Accepted Answer · 2017-07-24 20:39:07Z

1

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;

answered Jul 24, 2017 at 20:39

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

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

2 Comments

Caius Jard Over a year ago

Well, strictly speaking that doesn't use pivot, but it achieves the same result!

michael Over a year ago

Thanks krokodilko!

Rusan Constantin · Accepted Answer · 2017-07-24 20:32:44Z

0

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;

answered Jul 24, 2017 at 20:32

Rusan Constantin

1243 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related