0

All,

I need your help to merge the result in the same datetime in mysql.

I do a query of two tables like :

SELECT b.ID, b.DateTime, b.Value 
FROM tableA a, tableB b 
WHERE (a.Id=1) OR (a.Id=2) OR (a.Id=3) AND (a.Id = b.ID) 
      AND (b.DateTime Between '2011-04-02 06:00' And '2011-04-02 06:05')

Query result :

| ID  |    DateTime      | Value |
----------------------------------
| 1   | 2011-04-02 06:00 | 20    | 
| 1   | 2011-04-02 06:05 | 21    | 
| 2   | 2011-04-02 06:00 | 10    | 
| 2   | 2011-04-02 06:05 | 16    | 
| 3   | 2011-04-02 06:00 | 23    | 
| 3   | 2011-04-02 06:05 | 22    |

All i want is to make a result like : 

|    DateTime      | ID_1    | ID_2   | ID_3   | 
------------------------------------------------
| 2011-04-02 06:00 |  20     |   10   | 23     | 
| 2011-04-02 06:05 |  21     |   16   | 22     |

I hope there is a solution for this.

Thank you before for all your help.

Regards,
YougyZ

Sirko
  • 72,589
  • 19
  • 149
  • 183
YougyZ
  • 509
  • 1
  • 4
  • 10
  • This is called a pivot table and there are many answers to this problem on SO, e.g. http://stackoverflow.com/questions/5846007/sql-query-to-pivot-a-column-using-case-when – liquorvicar Apr 02 '12 at 09:30
  • Thanks, the difficulty is when i use the datetime, but i will try it.. I will let you know after it.. – YougyZ Apr 02 '12 at 09:36
  • Thanks @liquorvicar for the solution... I have tried this query : `SELECT b.Date, SUM(CASE WHEN a.Id=1 THEN b.Value ELSE 0 END) AS Mac1, SUM(CASE WHEN a.Id=2 THEN b.Value ELSE 0 END) AS Mac2, SUM(CASE WHEN a.Id=3 THEN b.Value ELSE 0 END) AS Mac3 FROM TableA a, TableB b WHERE (a.Id = b.ID) AND (b.Date Between '2011-04-02 06:00' And '2011-04-02 06:05') GROUP BY b.DA_Date` The Result is just like what i need.. :) – YougyZ Apr 02 '12 at 10:38
  • 1
    You can post this as an answer to your question and accept it. Then others won't waste time reading your question, since it's solved anyway. Thanks. – fancyPants Apr 02 '12 at 10:45
  • I want but i cant, because i have not enough points to do it, or i must wait until 8 hours after i post my question.. And Then, i can answer my question. Thanks btw.. – YougyZ Apr 02 '12 at 11:24

1 Answers1

0

Thanks @liquorvicar for the solution...

I have tried this query : 

SELECT b.Date, 
SUM(CASE WHEN a.Id=1 THEN b.Value ELSE 0 END) AS Mac1, 
SUM(CASE WHEN a.Id=2 THEN b.Value ELSE 0 END) AS Mac2, 
SUM(CASE WHEN a.Id=3 THEN b.Value ELSE 0 END) AS Mac3 
FROM TableA a, TableB b 
WHERE (a.Id = b.ID) AND (b.Date Between '2011-04-02 06:00' And '2011-04-02 06:05') 
GROUP BY b.DA_Date

The Result is just like what i need.. :)

YougyZ
  • 509
  • 1
  • 4
  • 10