mysql query by having timestamp along both the axis (i.e) in both columns and rows

Hi consider a table like this

Now i need an output like this

this is my requirement. i just gave 3 registration 开发者_高级运维dates days here. consider there are N number of registration dates and validity dates. how to write a query for this. Thanks for helping me.

What you seek is often called a crosstab. Here is a sample that would produce the results you want:

Select registrationDate
    , Sum( Case When ValidityDate = '2010-10-25' Then 1 Else 0 End ) As `2010-10-25`
    , Sum( Case When ValidityDate = '2010-10-26' Then 1 Else 0 End ) As `2010-10-26`
    , Sum( Case When ValidityDate = '2010-10-27' Then 1 Else 0 End ) As `2010-10-27`
From MyTable
Group By registrationDate

If what you want is to dynamically build the columns, you are seeking a dynamic crosstab. The SQL language in general is not designed around dynamic column generation and thus this cannot be done natively in MySQL without using dynamic SQL. Instead, you should build this query in your middle tier or report engine.