[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

MySQL query to transform table

Hi,

I need a mysql query to transform Table1 into Table2.

Thank you,


Table1
Name    |     Date        |   Hours    
John    |    2013-10-09  |      8
Mark    |    2013-10-10  |     7.20
Andy    |    2013-10-10 |    9.10
Mark    |    2013-10-09  |     8.30
Mark    |    2013-10-11 |       8
 
Table2
 Name   |2013-10-09| 2013-10-10 | 2013-10-11
   John   |       8       |      -            |       -
   Mark   |      8.30   |     7.20        |      8
 Andy    |        -       |     9.10        |      -
0
Qw M
Asked:
Qw M
  • 2
  • 2
1 Solution
 
plusone3055Commented:
thats using a Pivot Table

I credit Jimhorn for this  link
http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx 

good examples here
0
 
Louis01Commented:
Q1: Would Table2's date columns be limited (like always from day 1 to 31 of a day) or should it be dynamic based on the data (in other words show a column for every date whether there are 1 or 100)

Q2: What about dates not in range? If Table 1 has data for 2013-10-01, 2013-10-02 and 2013-10-04 but not 2013-10-03, should it show 2013-10-03 with a 0 count?
0
 
Qw MAuthor Commented:
@Louis01:

Q1:  date columns will be limited (0-31).
Q2: should show the character "-".

Thank you!
0
 
Louis01Commented:
This should give you a good start:
SELECT
  [Name],
  SUM(IF(DAY([Date]) = 1, [Hours], NULL)) AS '01',
  SUM(IF(DAY([Date]) = 2, [Hours], NULL)) AS '02',
  ...
  SUM(IF(DAY([Date]) = 31, [Hours], NULL)) AS '31',
FROM
  Table1
WHERE
  [Date] between '2013-10-01' and '2013-10-31'
GROUP BY
  [Name];

Open in new window


The output in the date columns would be numeric. You can change it to VARCHAR and check for NULL and show "-" but it is often easier to show "-" for null in whatever application output the values.
0
 
Qw MAuthor Commented:
Thank you Louis01!
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now