Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

MySQL Pivot Columns to Rows and back to Columns

Tags:

mysql

I have looked at quite a few searches on the pivoting and I got the first part of the pivot going, but I am having a hard time figuring out how to go about the 2nd and 3rd pivots. I will show an end goal table after the current state and some explanation

my data is what the HTML snip outputs if you run it.

<html>
<head>
<meta http-equiv="Content-Type"
content="text/html; charset=UTF-8">
</head>
<body bgcolor="#FFFFFF">
<table border="1">
<tr>
  <td>ID</td>
  <td>Title</td>
  <td>5ba.&nbsp;Initiation&nbsp;Phase&nbsp;Start&nbsp;Date&nbsp;Planned</td>
  <td>5bb.&nbsp;Initiation&nbsp;Phase&nbsp;Start&nbsp;Date&nbsp;Actual</td>
  <td>5bc.&nbsp;Initiation&nbsp;Phase&nbsp;End&nbsp;Date&nbsp;Planned</td>
  <td>5bd.&nbsp;Initiation&nbsp;Phase&nbsp;End&nbsp;Date&nbsp;Actual</td>
  <td>5ca.&nbsp;Preliminary&nbsp;Design&nbsp;Phase&nbsp;Start&nbsp;Date&nbsp;Planned</td>
  <td>5cb.&nbsp;Preliminary&nbsp;Design&nbsp;Phase&nbsp;Start&nbsp;Date&nbsp;Actual</td>
  <td>5cc.&nbsp;Preliminary&nbsp;Design&nbsp;Phase&nbsp;End&nbsp;Date&nbsp;Planned</td>
  <td>5cd.&nbsp;Preliminary&nbsp;Design&nbsp;Phase&nbsp;End&nbsp;Date&nbsp;Actual</td>
  <td>5da.&nbsp;Design&nbsp;and&nbsp;Development&nbsp;Phase&nbsp;Start&nbsp;Date&nbsp;Planned</td>
  <td>5db.&nbsp;Design&nbsp;and&nbsp;Development&nbsp;Phase&nbsp;Start&nbsp;Date&nbsp;Actual</td>
  <td>5dc.&nbsp;Design&nbsp;and&nbsp;Development&nbsp;Phase&nbsp;End&nbsp;Date&nbsp;Planned</td>
  <td>5dd.&nbsp;Design&nbsp;and&nbsp;Development&nbsp;Phase&nbsp;End&nbsp;Date&nbsp;Actual</td>
  <td>5ea.&nbsp;Qualification&nbsp;Phase&nbsp;Start&nbsp;Date&nbsp;Planned</td>
  <td>5eb.&nbsp;Qualification&nbsp;Phase&nbsp;Start&nbsp;Date&nbsp;Actual</td>
  <td>5ec.&nbsp;Qualification&nbsp;Phase&nbsp;End&nbsp;Date&nbsp;Planned</td>
  <td>5ed.&nbsp;Qualification&nbsp;Phase&nbsp;End&nbsp;Date&nbsp;Actual</td>
</tr>
<tr>
  <td>109581</td>
  <td>Proj 1</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
</tr>
<tr>
  <td>113075</td>
  <td>Proj 2</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
</tr>
<tr>
  <td>132510</td>
  <td>Proj 3</td>
  <td>2017-10-2</td>
  <td>2017-10-2</td>
  <td>2017-10-13</td>
  <td>2017-10-13</td>
  <td>2017-10-16</td>
  <td>2017-10-16</td>
  <td>2017-12-29</td>
  <td>0000-0-0</td>
  <td>2018-3-1</td>
  <td>0000-0-0</td>
  <td>2018-8-31</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
</tr>
<tr>
  <td>132511</td>
  <td>Proj 4</td>
  <td>2017-10-2</td>
  <td>2017-10-12</td>
  <td>2017-10-13</td>
  <td>2017-10-13</td>
  <td>2017-10-16</td>
  <td>2017-10-16</td>
  <td>2017-12-29</td>
  <td>2017-12-29</td>
  <td>2018-1-2</td>
  <td>2018-1-2</td>
  <td>2018-5-11</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
</tr>
<tr>
  <td>114171</td>
  <td>Proj 5</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
</tr>
<tr>
  <td>114200</td>
  <td>Proj 6</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
</tr>
</table>
</body>
</html>

So at first I tried just pivoting the column names, but that isn't going to work.

The end state of the table needs to look as follows

<html>
<head>
<meta http-equiv="Content-Type"
content="text/html; charset=UTF-8">
</head>
<body bgcolor="#FFFFFF">
<table border="1">
<tr>
  <td>ID</td>
  <td>Title</td>
  <td>Phase</td>
  <td>Start&nbsp;Date&nbsp;Planned</td>
  <td>Start&nbsp;Date&nbsp;Actual</td>
  <td>End&nbsp;Date&nbsp;Planned</td>
  <td>End&nbsp;Date&nbsp;Actual</td>
</tr>
<tr>
  <td>109581</td>
  <td>Proj 1</td>
  <td>Initiation</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
</tr>
<tr>
  <td>109581</td>
  <td>Proj 1</td>
  <td>Prelimenary Design</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
</tr>
<tr>
  <td>109581</td>
  <td>Proj 1</td>
  <td>Design and Development</td>
  <td>2017-10-2</td>
  <td>2017-10-13</td>
  <td>2017-10-13</td>
  <td>2017-10-16</td>
</tr>
<tr>
  <td>109581</td>
  <td>Proj 1</td>
  <td>Qualification Phase</td>
  <td>2017-10-12</td>
  <td>2017-10-13</td>
  <td>2017-10-13</td>
  <td>2017-10-16</td>
</tr>
<tr>
  <td>123456</td>
  <td>Proj 2</td>
  <td>Initiation</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
</tr>
<tr>
  <td>123456</td>
  <td>Proj 2</td>
  <td>Prelimenary Design</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
  <td>0000-0-0</td>
</tr>
<tr>
  <td>123456</td>
  <td>Proj 2</td>
  <td>Design and Development</td>
  <td>2017-10-2</td>
  <td>2017-10-13</td>
  <td>2017-10-13</td>
  <td>2017-10-16</td>
</tr>
<tr>
  <td>123456</td>
  <td>Proj 2</td>
  <td>Qualification Phase</td>
  <td>2017-10-12</td>
  <td>2017-10-13</td>
  <td>2017-10-13</td>
  <td>2017-10-16</td>
</tr>
</table>
</body>
</html>

So when I did the first pivot I realized I have a format that is still not very usable because it just makes the columns rows and the dates still aren't columns the way it's needed.

here was the first Query to start pivoting

    SELECT
    ID
    ,Title
    ,`5ba. Initiation Phase Start Date Planned` Date
    , 'Initiation Phase Start Date Planned' Phase
FROM RandD_Playbook
UNION ALL
SELECT
    ID
    ,Title
    ,`5bb. Initiation Phase Start Date Actual`
    ,'Initiation Phase Start Date Actual' 
FROM RandD_Playbook
UNION ALL
SELECT
    ID
    ,Title
    ,`5bc. Initiation Phase End Date Planned`
    ,'Initiation Phase End Date Planned' 
FROM RandD_Playbook
UNION ALL
SELECT
    ID
    ,Title
    ,`5bd. Initiation Phase End Date Actual`
    ,'Initiation Phase End Date Actual' 
FROM RandD_Playbook
UNION ALL
SELECT
    ID
    ,Title
    ,`5ca. Preliminary Design Phase Start Date Planned`
    ,'Preliminary Design Phase Start Date Planned' 
FROM RandD_Playbook
UNION ALL
SELECT
    ID
    ,Title
    ,`5cb. Preliminary Design Phase Start Date Actual`
    ,'Preliminary Design Phase Start Date Actual' 
FROM RandD_Playbook
UNION ALL
SELECT
    ID
    ,Title
    ,`5cc. Preliminary Design Phase End Date Planned`
    ,'Preliminary Design Phase End Date Planned' 
FROM RandD_Playbook
UNION ALL
SELECT
    ID
    ,Title
    ,`5cd. Preliminary Design Phase End Date Actual`
    ,'Preliminary Design Phase End Date Actual' 
FROM RandD_Playbook
UNION ALL
SELECT
    ID
    ,Title
    ,`5da. Design and Development Phase Start Date Planned`
    ,'Design and Development Phase Start Date Planned' 
FROM RandD_Playbook
UNION ALL
SELECT
    ID
    ,Title
    ,`5db. Design and Development Phase Start Date Actual`
    ,'Design and Development Phase Start Date Actual' 
FROM RandD_Playbook
UNION ALL
SELECT
    ID
    ,Title
    ,`5dc. Design and Development Phase End Date Planned`
    ,'Design and Development Phase End Date Planned' 
FROM RandD_Playbook
UNION ALL
SELECT
    ID
    ,Title
    ,`5dd. Design and Development Phase End Date Actual`
    ,'Design and Development Phase End Date Actual' 
FROM RandD_Playbook
UNION ALL
SELECT
    ID
    ,Title
    ,`5ea. Qualification Phase Start Date Planned`
    ,'Qualification Phase Start Date Planned' 
FROM RandD_Playbook
UNION ALL
SELECT
    ID
    ,Title
    ,`5eb. Qualification Phase Start Date Actual`
    ,'Qualification Phase Start Date Actual' 
FROM RandD_Playbook
UNION ALL
SELECT
    ID
    ,Title
    ,`5ec. Qualification Phase End Date Planned`
    ,'Qualification Phase End Date Planned' 
FROM RandD_Playbook
UNION ALL
SELECT
    ID
    ,Title
    ,`5ed. Qualification Phase End Date Actual`
    ,'Qualification Phase End Date Actual' 
FROM RandD_Playbook

I'd appreciate some guidance on this and the best way to consolidate the table the way I am being asked.

like image 320
Shenanigator Avatar asked Nov 07 '22 10:11

Shenanigator

1 Answers

You need to select the Phase as constant and the four corresponding columns. One subquery per phase which are combined by UNION ALL:

SELECT ID, Title,
    'Initiation' as Phase,
    `5ba. Initiation Phase Start Date Planned` as `Start Date Planned`,
    `5bb. Initiation Phase Start Date Actual`  as `Start Date Actual`,
    `5bc. Initiation Phase End Date Planned`   as `End Date Planned`,
    `5bd. Initiation Phase End Date Actual`    as `End Date Actual`
FROM RandD_Playbook
UNION ALL
SELECT ID, Title,
   'Preliminary Design' as Phase,
    `5ca. Preliminary Design Phase Start Date Planned` as `Start Date Planned`,
    `5cb. Preliminary Design Phase Start Date Actual`  as `Start Date Actual`, 
    `5cc. Preliminary Design Phase End Date Planned`   as `End Date Planned`,  
    `5cd. Preliminary Design Phase End Date Actual`    as `End Date Actual`    
FROM RandD_Playbook
UNION ALL
SELECT ID, Title,
    'Design and Development' as Phase,
    `5da. Design and Development Phase Start Date Planned` as `Start Date Planned`,
    `5db. Design and Development Phase Start Date Actual`  as `Start Date Actual`,
    `5dc. Design and Development Phase End Date Planned`   as `End Date Planned`,
    `5dd. Design and Development Phase End Date Actual`    as `End Date Actual`
FROM RandD_Playbook
UNION ALL
SELECT ID, Title,
    'Qualification' as Phase,
    `5ea. Qualification Phase Start Date Planned` as `Start Date Planned`,
    `5eb. Qualification Phase Start Date Actual`  as `Start Date Actual`,
    `5ec. Qualification Phase End Date Planned`   as `End Date Planned`,
    `5ed. Qualification Phase End Date Actual`    as `End Date Actual`
FROM RandD_Playbook
ORDER BY Title

The result from your sample data:

|     ID |  Title |                  Phase | Start Date Planned | Start Date Actual | End Date Planned | End Date Actual |
|--------|--------|------------------------|--------------------|-------------------|------------------|-----------------|
| 109581 | Proj 1 |             Initiation |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 109581 | Proj 1 |          Qualification |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 109581 | Proj 1 | Design and Development |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 109581 | Proj 1 |     Preliminary Design |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 113075 | Proj 2 | Design and Development |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 113075 | Proj 2 |          Qualification |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 113075 | Proj 2 |     Preliminary Design |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 113075 | Proj 2 |             Initiation |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 132510 | Proj 3 | Design and Development |         2018-03-01 |        0000-00-00 |       2018-08-31 |      0000-00-00 |
| 132510 | Proj 3 |     Preliminary Design |         2017-10-16 |        2017-10-16 |       2017-12-29 |      0000-00-00 |
| 132510 | Proj 3 |             Initiation |         2017-10-02 |        2017-10-02 |       2017-10-13 |      2017-10-13 |
| 132510 | Proj 3 |          Qualification |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 132511 | Proj 4 |          Qualification |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 132511 | Proj 4 | Design and Development |         2018-01-02 |        2018-01-02 |       2018-05-11 |      0000-00-00 |
| 132511 | Proj 4 |             Initiation |         2017-10-02 |        2017-10-12 |       2017-10-13 |      2017-10-13 |
| 132511 | Proj 4 |     Preliminary Design |         2017-10-16 |        2017-10-16 |       2017-12-29 |      2017-12-29 |
| 114171 | Proj 5 |             Initiation |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 114171 | Proj 5 |          Qualification |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 114171 | Proj 5 | Design and Development |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 114171 | Proj 5 |     Preliminary Design |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 114200 | Proj 6 |     Preliminary Design |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 114200 | Proj 6 |          Qualification |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 114200 | Proj 6 | Design and Development |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |
| 114200 | Proj 6 |             Initiation |         0000-00-00 |        0000-00-00 |       0000-00-00 |      0000-00-00 |

Demo: http://sqlfiddle.com/#!9/e14ce6/6

like image 86
Paul Spiegel Avatar answered Nov 09 '22 22:11

Paul Spiegel
Sign in to Comment

Related questions

using PHP & MySQL... How do I free memory?

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!