?
Solved

MySQL query to transform table

Posted on 2013-11-06
5
Medium Priority
?
257 Views
Last Modified: 2013-11-06
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
Comment
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
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 22

Expert Comment

by:plusone3055
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

by:Louis01
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

by:Qw M
ID: 39627334
@Louis01:

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

Thank you!
0
 
LVL 11

Accepted Solution

by:
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];

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
 

Author Comment

by:Qw M
ID: 39627381
Thank you Louis01!
0

Featured Post

Congratulations! You’re Certified – Now What?

Starting a new career can be overwhelming. Becoming certified in your field of expertise is a great start, but where do you go from here?  Here are some tips to help you on your career journey.

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.
Video by: ITPro.TV
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…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question