CODEWITHSUNDEEP
mysqlsql
Baub
Baub

Reputation: 751

Consolidate columns into one column

I have a view which produces data like this:

Event       Date        US_City     CA_City     ME_City     Br_City
Baseball    6/3/2016    LA          
Darts       5-Jun                   Ont     
Cricket     5-Jun                               Mexico City 
Football    6-Jun                                           Rio De Janero

What I want to end up with is something like this, where the city is in one column and not spread across multiple columns:

Event       Date        City    
Baseball    6/3/2016    LA          
Darts       5-Jun       Ont     
Cricket     5-Jun       Mexico City 
Football    6-Jun       Rio De Janero

I wasn't able to find a way to do this in SQL (more specifically, MySQL). Are there aggregate functions or similar that I can use here?

Upvotes: 0

Views: 45

Answers (1)

Haytem BrB
Haytem BrB

Reputation: 1499

Use:

Select Event, Date, coalesce(US_City,CA_City,ME_City,Br_City) city
from yourtable

Coalesce function returns the first non-NULL value in the list of cities, and assign it to City

Upvotes: 2

Related Questions