Pivot Results By Day I have a table in SQL Server 2008, with mails received from a pop account.

The table has the following columns:

+------+----+---------+------+------+
| from | to | subject | body | date |
+------+----+---------+------+------+


I need a query to get a result of the number of mails every sender (from) has sent by day (a column for each day).

The result I need is:

+------------------+----------+-----------+-------------+------------+-----+
|      Sender      | Monday 5 | Tuesday 6 | Wednesday 7 | Thursday 8 | ... |
+------------------+----------+-----------+-------------+------------+-----+
| example@me.com   |        1 |         2 |           2 |          0 |     |
| example2@you.com |        2 |         3 |           4 |          5 |     |
+------------------+----------+-----------+-------------+------------+-----+


I think it has to be done with pivot but not sure how to write it.
I agree a pivot should get you what you need, but you should be aware that the column names are going to be fixed.  Something like the following should work. You could also extend this example to use dynamic column names if you use dynamic SQL.

SELECT 
    *
FROM 
(
    SELECT 
        [from]  AS [Sender]
        , DATENAME(DW, [date]) + N'' + CAST(DATEPART(DAY, [date]) AS NVARCHAR(4)) AS [FormmattedDay] -- This output must match the 'IN' clause below
    FROM &lt;YourTable&gt;
) as s
PIVOT
(
    COUNT([from]) -- Aggregate
    FOR [FormmattedDay] IN ([Monday 5], [Tuesday 6], [Wednesday 7], etc.) -- Match to pivoted columns
)as [pivot]


Here is my standard disclaimer for PIVOT queries.  Try not to do them if you don't have to, and let the application layer format the data instead.  If you go that route your query would be much nicer.