How can I join these two tables to get a new table with new columns including the values?

Question

I have two tables like this

id   Value   color
-------------------
1   Shirt    red
1   Bag     blue
1   Shoes    blue
1   ....

Note that we can have any number of items in this table.

id   Name   Gender
-------------------
1    Mary    F

Is there any SQL queries that would give me the following table?

id   Name   Gender  Shirt   Bag  ...
-------------------------------------
1    Mary    F       red    blue  ...

Thanks

Are Shirt and bag the only two distinct values of the Value column of the first table? (if yes, you can use a pivot) — Vincent Pan, Sep 23 '16 at 15:37

score 2 · Answer 1 · answered Sep 23 '16 at 15:38

2

You can join them like this:

SELECT  t2.id,
        t2.Name,
        t2.Gender,
        MIN(CASE WHEN t1.Value = 'Shirt' THEN color END) Shirt,
        MIN(CASE WHEN t1.Value = 'Bag' THEN color END) Bag
FROM Table1 t1
INNER JOIN Table2 t2
    ON t1.id = t2.id
GROUP BY t2.id,
         t2.Name,
         t2.Gender;

answered Sep 23 '16 at 15:38

Lamak

69,480
12
108
116

Note that we can have any number of items in the first table and the values are not fixed. – Alex Sep 23 '16 at 15:45
@Alex well, I couldn't note that, because it wasn't in your question. For that, you'll need dynamic SQL or something similar in mySQL – Lamak Sep 23 '16 at 15:49

How can I join these two tables to get a new table with new columns including the values?

1 Answers1