I have a mysql database that looks like this:

proj_id | job1_name | job1_type | job1_time | job2_name | job2_type | job2_time | ...
--------+-----------+-----------+-----------+-----------+-----------+-----------| ...
      1 | John      | Roof      |        10 | Jane      | Floor     |         2 | ...

Actual table has these three fields (name, type, and time) for up to 5 jobs.

I need the results to display in a table like this:

proj_id |  Name  |   Type   | Time
      1 | John   | Roof     |   10
      1 | Jane   | Floor    |    2

How can I accomplish this?

thanks for all the help!!


  • 1
    To clarify - are job1name, 'job1_type, job1_time, job2_name, etc. all separate columns in your table, or are they a comma-separated list of values stored in a single column in your table? And are there really just two job "slots" per row? Can the second job "slot" be empty? If so, would you not want a row produced for it? What would qualify it as empty - all columns NULL, any 1 column NULL, etc? – RDFozz Jul 24 '17 at 17:29
  • 1
    Thanks RDFozz, yes they are all separate columns within the table. There are actually 5 job slots, and only 1 out of five is possible depending on the record. I would prefer not to display empty job slots. If any of the fields for that respective job is blank/null then it doesn't need to be displayed. – jcapo8856 Jul 24 '17 at 17:44

Using Union, you can get the results as you want. But I'm not sure whether it is a good solution.

SELECT `proj_id`,`job1_name` AS 'Name',`job1_type` AS 'Type',`job1_time` AS 'Time'
FROM `job`
SELECT `proj_id`,`job2_name` AS 'Name',`job2_type` AS 'Type',`job2_time` AS 'Time'
FROM `job`

Why you are having 5 jobs data in different fields. You can add job_id for the reference and other fields as common.

  • Welcome to DBA.SE! The best answers include details to help the OP resolve their problem. This is more of a comment. As you buildup a reputation here, you'll gain the ability to comment on others' questions and answers. – RDFozz Jul 24 '17 at 18:54
  • Thank you for your response! Each RECORD represents a PROJECT. So each project has 5 potential JOBS associated to it. At the time, I wasn't thinking properly. I tried your syntax, but it only displays the top select statement and not the 2nd. So my results are only for 'job1' and nothing for the other jobs. – jcapo8856 Jul 24 '17 at 19:59
  • This can be resolved by simply putting alias in the select statement. – Tamilvanan N Jul 24 '17 at 20:13
  • Check dbfiddle.uk/… - This is normally called an UNPIVOT. – joanolo Jul 24 '17 at 20:22
  • Thanks you Tamilvanan and Joanolo for your answers! That worked! – jcapo8856 Jul 24 '17 at 21:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.