Displaying table as grid

Hello,

>From the following table showing which countries people have been to

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

wrote in message
news:1143388677.989608.156150@i40g2000cwc.googlegroups.com.. .
> Hello,
>
>>From the following table showing which countries people have been to
>
> 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

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
http://dev.mysql.com/tech-resources/articles/wizard/index.ht ml.

Here's another possibility:

SELECT DISTINCT t.Name,
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 =
'France'
LEFT OUTER JOIN myTable AS c2 ON t.Name = c2.Name AND c2.Countries =
'Italy'
LEFT OUTER JOIN myTable AS c3 ON t.Name = c3.Name AND c3.Countries =
'Germany'
LEFT OUTER JOIN myTable AS c4 ON t.Name = c4.Name AND c4.Countries =
'UK'
LEFT OUTER JOIN myTable AS c5 ON t.Name = c5.Name AND c5.Countries =
'USA';

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.

Regards,
Bill K.

Re: Displaying table as grid

Thanks. That worked perfectly! :-)

Is it possible to sort the columns produced?

Re: Displaying table as grid

On Tue, 28 Mar 2006 12:52:19 -0800, wrote:

> Is it possible to sort the columns produced?

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.

Regards,
Bill K.