Displaying table as grid

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

Quoted text here. Click to load it

Name    Countries

Fred      France
Fred      UK
Bill      Germany
Jack      USA
Bill      UK
Tim       USA
Tim       France
Tim       Italy

what SQL statement can I use in MySQL to format it like this

Name    France    Italy    Germany    UK    USA
Bill            Yes    Yes
Fred    Yes                Yes
Jack                        Yes
Tim     Yes    Yes        Yes

It's like an excel pivot table but lots of googling for things like
that in SQL found nothing.

Re: Displaying table as grid

Quoted text here. Click to load it

Well, I can suggest a couple of solutions that work for the example of the
five specific countries you give above.

  SELECT t.Name,
    SUM(IF(t.Countries = 'France', 1, 0)) AS `France`,
    SUM(IF(t.Countries = 'Italy', 1, 0)) AS `Italy`,
    SUM(IF(t.Countries = 'Germany', 1, 0)) AS `Germany`,
    SUM(IF(t.Countries = 'UK', 1, 0)) AS `UK`,
    SUM(IF(t.Countries = 'USA', 1, 0)) AS `USA`
  FROM myTable AS t
  GROUP BY t.Name

The above solution is based on the article at

Here's another possibility:

    IF(c1.Countries IS NULL, '', 'Yes'),
    IF(c2.Countries IS NULL, '', 'Yes'),
    IF(c3.Countries IS NULL, '', 'Yes'),
    IF(c4.Countries IS NULL, '', 'Yes'),
    IF(c5.Countries IS NULL, '', 'Yes')
  FROM myTable AS t
    LEFT OUTER JOIN myTable AS c1 ON t.Name = c1.Name AND c1.Countries =
    LEFT OUTER JOIN myTable AS c2 ON t.Name = c2.Name AND c2.Countries =
    LEFT OUTER JOIN myTable AS c3 ON t.Name = c3.Name AND c3.Countries =
    LEFT OUTER JOIN myTable AS c4 ON t.Name = c4.Name AND c4.Countries =
    LEFT OUTER JOIN myTable AS c5 ON t.Name = c5.Name AND c5.Countries =

The solutions above might not work well in your case, because you need to
hardcode the country names.  I assume the list of countries could grow, so
hardcoding them in the query is not going to be work.  Most crosstab or
pivot-table solutions I have read rely on the set of columns being fixed and
relatively few in number.

Another option is to use GROUP_CONCAT:

  SELECT t.Name, GROUP_CONCAT(t.Countries ORDER BY t.Countries) AS Countries
  FROM myTable AS t
  GROUP BY t.Name

This outputs two columns: the name, and a string containing the
comma-separated list of countries:

Bill     Germany,UK
Fred    France,UK
Jack    USA
Tim    France,Italy,USA

You'd have to split the comma-separated string in your application to put
the values into the proper columns to make it into a grid.  But this
solution does work even if the number of distinct countries grows.  No need
to hard-code the countries in the SQL query.

Bill K.

Re: Displaying table as grid

Thanks.  That worked perfectly! :-)

Is it possible to sort the columns produced?

Re: Displaying table as grid

Quoted text here. Click to load it

Sort by which columns?  t.Name or the result of GROUP_CONCAT()?

You can always "ORDER BY 2" to sort by the second column in the  
select-list, even if it's an expression and you haven't given it an alias.

Bill K.

Site Timeline