获取具有行的Max值的列和行

Question

table: |name|US|IN|SA|CHN|AU|EU|.......
       |john|0 |12|2 |0  |1 |0 |.......
       |andy|20|17|2 |10 |1 |0 |.......

I have a table with 200 columns having all countries. In them are the entries in integer of the number of clicks coming from each country. In order to display them in a geo chart I would like to parse these values in such a way as follows:

output:

IN,12
SA,2
AU,1

such that I would like to exlude those countries that have 0 clicks and grouped by username john

Here is my method but it is incomplete..

 $query = "SELECT * FROM `countries`";


    $result = mysql_query("SHOW COLUMNS FROM `countries`.`graph_geo_table` WHERE Field NOT IN ('Name')");
    if (!$result) {
        echo 'Could not run query: ' . mysql_error();
        exit;
    }

if (mysql_num_rows($result) > 0) {
    while ($row = mysql_fetch_assoc($result)) {

    echo $row[Field];

    }
}

Answer 1

You can do it like this

SELECT column_name country,
       CASE column_name
       WHEN 'US'  THEN us
       WHEN 'IN'  THEN `in`
       WHEN 'SA'  THEN sa
       WHEN 'CHN' THEN chn
       WHEN 'AU'  THEN au
       WHEN 'EU'  THEN eu
       END clicks
  FROM Table1 t CROSS JOIN
(
  SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_NAME = 'Table1'
     AND TABLE_SCHEMA = SCHEMA()
     AND COLUMN_NAME <> 'name'
) c  
 WHERE name = 'john'
HAVING clicks > 0
 ORDER BY clicks DESC

Output:

| COUNTRY | CLICKS |
|---------|--------|
|      IN |     12 |
|      SA |      2 |
|      AU |      1 |

Here is SQLFiddle demo