CODEWITHSUNDEEP
mysql
Winds
Winds

Reputation: 397

switching MySQL columns into rows

I have a column in mysql and would like to change the column into [0 1 0 1] type for data processing,

for example,

+------+
| area |
+------+
| 1    |
| 2    |
+------+

I would like to change the form above into the following form:

+--------+--------+
| apple  | banana |
+--------+--------+
|   1    |   0    |
|   0    |   1    |
+--------+--------+

in short if area = '1', then apple = 1 if area = '2', then banana = 1

My code is as follows after referencing to some websites but it doesn't work..

insert into test1 (apple, banana)
    -> select count(case when area = '1' then 1 else 0 end) as apple, 
    -> select count(case when area = '2' then 1 else 0 end) as banana
    -> from test3;

the error says "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select count(case when area = '2' then 1 else 0 end) as banana from test3' at line 3", but I cannot find any reasons.. could someone please kindly help me..?

Thanks ..>_<

Upvotes: 0

Views: 42

Answers (2)

valex
valex

Reputation: 24134

SELECT
   CASE WHEN area = 1 THEN 1 ELSE 0 END  as apple,
   CASE WHEN area = 2 THEN 1 ELSE 0 END as banana
FROM T

SQL fiddle demo

Upvotes: 1

Smita Ahinave
Smita Ahinave

Reputation: 1888

If you have predefined keywords for each id (like 1 for apple,2 for banana) then you may do it like this...

select SUM(case when area = '1' then 1 else 0 end) as apple,0 as banana from test3
union select 0 ,SUM(case when area = '2' then 1 else 0 end) from test3;

Upvotes: 1

Related Questions