Talk:MySQL/Pivot table

From Wikibooks, the open-content textbooks collection

Very good explanations, but this algorithm supposes that you know the number and the categories/values you will find in your database for each column. What if you do not know?

My records are as follows: product, date, sales. And I can of course have several sales for the same product on the same day, but this is not the main point. I need a table looking like this:

	Date 1	...	Date N
Product 1	Sum 1	...	Sum N
...
Product N	Sum 1	...	Sum N

Does anybody knows of a way to process such a table using SQL?
Thanks.
Alain Joaris

I'd say you need to build your query with a programming language (see http://search.cpan.org/~gmax/DBIx-SQLCrosstab-1.17/SQLCrosstab.pm) Beuc

In my opinion, the following code can be used for numeric and non-numeric columns:

select name, sum(score*(exam=1)) as exam1, sum(score*(exam=2)) as exam2, sum(score*(exam=3)) as exam3, sum(score*(exam=4)) as exam4 from exams group by name;

Jan Porsius