Display column name with max value between several columns

Question

I have data I have collect from a form. And have "pivoted" the data so it looks like this:

COUNTY     | denver  | seattle   | new_york | dallas   | san fran
-----------+---------+-----------+----------+----------+---------
ada        | 3       | 14        | 0        | 0        | 0    
slc        | 10      | 0         | 0        | 0        | 9    
canyon     | 0       | 5         | 0        | 0        | 0    
washington | 0       | 0         | 11       | 0        | 0    
bonner     | 0       | 0         | 0        | 2        | 0

(This was accomplished using case statements, crosstab is not allowed in the environment I am using: cartodb)

I now need a column that list the CITY with the max value. For example:

COUNTY     | CITY     | denver  | seattle   | new_york | dallas   | san fran
-----------+----------+---------+-----------+----------+----------+---------
ada        | seattle  | 3       | 14        | 0        | 0        | 0    
slc        | denver   | 10      | 0         | 0        | 0        | 9    
canyon     | seattle  | 0       | 5         | 0        | 0        | 0    
washington | new_york | 0       | 0         | 11       | 0        | 0    
bonner     | dallas   | 0       | 0         | 0        | 2        | 0

Thanks for the replies. I am wondering if I can make this happen in one query. For example, here is the query I used to get my data into the first table example above, whick pivoted the data:

SELECT counties.name, counties.state, counties.the_geom,
count(case when fandom_survey_one.favorite_team = 'Arizona Cardinals' then 'ari' end)                                       ari,
count(case when fandom_survey_one.favorite_team = 'Atlanta Falcons' then 'atl' end) atl,
count(case when fandom_survey_one.favorite_team = 'Baltimore Ravens' then 'bal' end) bal,
count(case when fandom_survey_one.favorite_team = 'Buffalo Bills' then 'buf' end) buf,
count(case when fandom_survey_one.favorite_team = 'Carolina 
FROM fandom_survey_one, counties
WHERE ST_Intersects(fandom_survey_one.the_geom, counties.the_geom)
group by counties.name, counties.state, counties.the_geom
order by counties.name, counties.state

I wondering if there is a way to incorporate the answers provided by Gordon or Erwin into this first query to be able to do this all in one query. THanks.

Answer 1

That's a textbook example for a "simple" or "switched" CASE statement to avoid code repetition.

SELECT CASE greatest(denver, seattle, new_york, dallas, "san fran")
          WHEN denver      THEN 'denver'
          WHEN seattle     THEN 'seattle'
          WHEN new_york    THEN 'new_york'
          WHEN dallas      THEN 'dallas'
          WHEN "san fran"  THEN 'san fran'
       END AS city, *
FROM   tbl;

The first in the list (from left to right) wins in case of a tie.