Changing a product drop down attribute with multi-select in Magento is not an easy task because there is no option available in Magento administrator. So what's next? The answer is, we can change it from the database. We have to follow simple three steps and attribute will be changed.
Before going to run queries, create a backup of your database and then follow below steps.
Run the below query that will update the drop down attribute to multi select.
UPDATE eav_attribute SET
entity_type_id = '4',
attribute_model = NULL,
backend_model = 'eav/entity_attribute_backend_array',
backend_type = 'varchar',
backend_table = NULL,
frontend_model = NULL,
frontend_input = 'multiselect',
frontend_class = NULL
WHERE attribute_id = '92';
Now copy the old attributes values from old table to new with this query.
INSERT INTO catalog_product_entity_varchar ( entity_type_id, attribute_id, store_id, entity_id, value)
SELECT entity_type_id, attribute_id, store_id, entity_id, value
WHERE attribute_id = 92;
After above queries, we must have delete old records from database because old records can conflict with new records. To delete old records, we will run below query.
DELETE FROM catalog_product_entity_int
WHERE entity_type_id = 4 and attribute_id = 92;
Note that, In my case attribute id is 92 (You can check your attribute id from 'eav_attribute' table.) and entity_type_id 4 (Check the entity_type_id of catalog_product (Column name 'entity_type_code') from eav_entity_type table ).
Now clear your cache and re-index the site. That's it.