select 5 dates of a week

Question

I have this table, Lineas (id, date, proyect_id, hours) and I want to select a sum of hours by date into a week. For example I need this output for dates between 01/01/2013 and 05/01/2013:

Proyect   | 01/01/2013 | 02/01/2013 | 03/01/2013 | 04/01/2013 | 05/01/2013
Proyect1  |     -      |     5      |     -      |     4      |     4
Proyect2  |     4      |     2      |     8      |     4      |     2
Proyect3  |     4      |     1      |     -      |     -      |     2

And this is the data within the table

   Id   |   Proyect_id   |    date    | hours
    1   |   Proyect1     | 02/01/2013 |   5
    2   |   Proyect1     | 04/01/2013 |   4
    3   |   Proyect1     | 05/01/2013 |   4
    4   |   Proyect2     | 01/01/2013 |   4
    5   |   Proyect2     | 02/01/2013 |   2
    6   |   Proyect2     | 03/01/2013 |   8
    7   |   Proyect2     | 04/01/2013 |   4
    8   |   Proyect2     | 05/01/2013 |   2
    9   |   Proyect3     | 01/01/2013 |   4
    10  |   Proyect3     | 02/01/2013 |   1
    11  |   Proyect3     | 05/01/2013 |   2

Any help or clue?

Thanks in advance

Answer 1

MySQL does not have a pivot function so you will need to replicate the ability using an aggregate function with a CASE expression.

If your values are known, then you can hard-code them similar to this:

select Proyect_id,
  sum(case when date = '2013-01-01' then hours end) `2013-01-01`,
  sum(case when date = '2013-02-01' then hours end) `2013-02-01`,
  sum(case when date = '2013-03-01' then hours end) `2013-03-01`,
  sum(case when date = '2013-04-01' then hours end) `2013-04-01`
from Lineas
group by Proyect_id

See SQL Fiddle with Demo

But if the values are unknown then you will want to use a prepared statement to generate dynamic SQL:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(case when date = ''',
      date,
      ''' then hours else 0 end) AS `',
      date, '`'
    )
  ) INTO @sql
FROM  Lineas;

SET @sql = CONCAT('SELECT Proyect_id, ', @sql, ' 
                  FROM Lineas 
                  GROUP BY Proyect_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo

Both give the result:

| PROYECT_ID | 2013-01-01 | 2013-02-01 | 2013-03-01 | 2013-04-01 |
------------------------------------------------------------------
|   Proyect1 |          0 |          5 |          0 |          4 |
|   Proyect2 |          4 |          2 |          8 |          4 |
|   Proyect3 |          4 |          1 |          0 |          0 |