I have a database with multiple mutually exclusive columns in it (don't ask me why, somebody created it like that. sigh). Below is an example of it.
-------------------------------------------
| ID | BLACK | WHITE | RED | GREEN | BLUE |
-------------------------------------------
| 1 | 1 | 0 | 0 | 0 | 0 |
-------------------------------------------
| 2 | 0 | 0 | 1 | 0 | 0 |
-------------------------------------------
| 3 | 0 | 1 | 0 | 0 | 0 |
-------------------------------------------
well you get the idea, it contains thousands of record. Now, how do I combine it into a single "COLOR" column. Like below.
--------------
| ID | COLOR |
--------------
| 1 | BLACK |
--------------
| 2 | RED |
--------------
| 3 | WHITE |
--------------
all I can think of right now is to do 5 of these
UPDATE table SET COLOR = 'BLACK' WHERE BLACK = 1
oh I forgot to mention, the table that I am currently working on contains at least 15 mutually exclusive columns! are there any simpler ways I can do this?
Edit
changed COLORS to COLOR
try to use coalesce() and concat() to combine columns in the SQL query. Eg: 1, vishnu, 9961907453, 91 will return phone number as +919961907453. You can use the following query to get the above result.
Similar to the UNION operator in SQL, even the INTERSECT operator has to abide by the following two rules for successful execution: The number and order of columns in both queries has to be the same. The data types of corresponding columns from both the select queries must be compatible with each other.
The GROUP BY clause is used along with some aggregate functions to group columns with the same values in different rows. The group by multiple columns technique retrieves grouped column values from one or more database tables by considering more than one column as grouping criteria.
Use a case-expression
, as in this abbreviated example:
update table
set color=
case
when black then "black"
when red then "red"
when blue then "blue"
end;
Here's a sqlfiddle
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With