How to distribute 1 column to 4 columns in mysql

Question

I have empty table below:

----------------
|id|c_id|c1|c2|c3|c4|
----------------
|  |    |  |  |  |  |

And another table:

|id|c_id|type|value|
--------------------
 1 |  1 | c1 | 234 |
-------------------
 2 |  1 | c2 | 211 |
-------------------
 3 |  3 | c2 |  4  |
--------------------
 5 |  4 | c3 |  54 |
--------------------
 5 |  5 | c4 | 789 |
--------------------
 6 |  2 | c1 | 234 |

I want to fill table1 with value from table2. As you can see column "type" will be devided into 4 columns in table 1. How I can do it with 1 query?

What i expect:

   |id|c_id| c1  | c2  |  c3  |  c4  |
    ----------------------------------
   |1 | 1  | 234 | 211 | null | null |
    ----------------------------------
   |2 | 2  | 234 | null| null | null | 
    ----------------------------------
   |3 | 3  | null|  4  | null | null |
    ----------------------------------
   |4 | 4  | null| null|  54  | null |
    ----------------------------------
   |5 | 5  | null| null| null | 789  |

@Barmar, For example in table2: value of type c1 will be at the c1 column in table1. — Шыназ Алиш, May 22 '15 at 07:46
It looks like you want to do a pivot, but there's no common ID to connect the rows. — Barmar, May 22 '15 at 07:46
But which value of type c2 goes with which value of type c1? — Barmar, May 22 '15 at 07:47
@Naeem, I don't know briefly what i should do to do it, therefore i ask here. — Шыназ Алиш, May 22 '15 at 07:47
You need another column that links the related rows together. — Barmar, May 22 '15 at 07:47
@ШыназАлиш Can you put the out put that you expect below the problem — KTAnj, May 22 '15 at 07:48
@Barmar, I edited my question, i put to table c_id that links columns — Шыназ Алиш, May 22 '15 at 07:58
What happens when type has two different values? what should be in c1 column when c_id = 1 and c1 has values 234 and 211? — Jakob, May 22 '15 at 08:00

score 1 · Answer 1 · edited May 23 '17 at 10:26

1

This is an ordinary pivot table operation:

INSERT INTO table1 (c_id, c1, c2, c3, c4)
SELECT c_id, MAX(CASE WHEN type = 'c1' THEN `value` END) AS c1,
             MAX(CASE WHEN type = 'c2' THEN `value` END) AS c2,
             MAX(CASE WHEN type = 'c3' THEN `value` END) AS c3,
             MAX(CASE WHEN type = 'c4' THEN `value` END) AS c4
FROM table2
GROUP BY c_id

DEMO

edited May 23 '17 at 10:26

Community

1
1

answered May 22 '15 at 08:01

Barmar

741,623
53
500
612

How to distribute 1 column to 4 columns in mysql

1 Answers1