Solved

# MySQL query to transform table

Posted on 2013-11-06
Medium Priority
257 Views
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
Question by:Qw M
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2

LVL 22

Expert Comment

ID: 39627276
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

LVL 11

Expert Comment

ID: 39627318
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

Author Comment

ID: 39627334
@Louis01:

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

Thank you!
0

LVL 11

Accepted Solution

Louis01 earned 2000 total points
ID: 39627351
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];
``````

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

Author Comment

ID: 39627381
Thank you Louis01!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
###### Suggested Courses
Course of the Month10 days, 17 hours left to enroll