CODEWITHSUNDEEP
mysqlsql
Pablohoney
Pablohoney

Reputation: 104

dummify a column

I'm working on the same old death causes database with this structure:

+--------------------+---------------+------+-----+---------+-------+
| Field              | Type          | Null | Key | Default | Extra |
+--------------------+---------------+------+-----+---------+-------+
| decesso            | int(1)        | NO   |     | NULL    |       |
| anno               | year(4)       | NO   |     | NULL    |       |
| mese               | int(2)        | NO   |     | NULL    |       |
| data               | varchar(10)   | NO   |     | NULL    |       |
| age                | int(3)        | NO   |     | NULL    |       |
| sex                | enum('M','F') | NO   |     | NULL    |       |
| cod_comune         | int(6)        | NO   |     | NULL    |       |
| nome_comune        | varchar(40)   | NO   |     | NULL    |       |
| causa_fin          | varchar(4)    | NO   |     | NULL    |       |
| cod_tit            | varchar(3)    | YES  |     | NULL    |       |
+--------------------+---------------+------+-----+---------+-------+

in practice, every row is a death record, no matter the cause, the town, the age and stuff. In particular, the column cod_tit contains the code for the macro family of diseases, such as I = cancer, II = heart diseases and so on; this column is therefore composed like I,I,IX,XX,...

What I need is to have one column for every disease and values 0 if the guy died for another cause and 1 if he died for that very cause; something like a matrix like this

+--------------------------------------------+
| age | sex | town | dis_I | dis_II | dis_III|
+--------------------------------------------+
| 85  | M   | 0001 | 1     | 0      | 0      |
| 59  | M   | 0001 | 0     | 0      | 1      |
| 78  | F   | 0200 | 0     | 1      | 0      |
| 90  | M   | 0032 | 0     | 0      | 2      |
| ......                                     |
| and so on                                  |
+--------------------------------------------+

is that possible in mysql?

Upvotes: 0

Views: 51

Answers (1)

Mudassir Hasan
Mudassir Hasan

Reputation: 28771

You can use CASE statement

SELECT age,sex,town,
       CASE cod_tit WHEN 'I' THEN 1 ELSE 0 END AS dis_I ,
       CASE cod_tit WHEN 'II' THEN 1 ELSE 0 END AS dis_II ,
...
FROM tableName

Upvotes: 1

Related Questions