Skip to Main Content

MySQL Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

MySQL - Transpose column to rows and project column values as new columns

798542Aug 7 2018 — edited Aug 16 2018

I required to transpose column values to row in MySQL. It also required single column with it date values being projected as different columns itself in result set.

As shown below, values are seprated by pipe "|" -

Proj|Test_Cycle|eDate|In_Scope|Expected_Value|Actual_Value|Diff

---------------------------------------------------------------

ABC|Test1|2018-08-01|349|12|5|7

XYZ|Test1|2018-08-01|68|31|34|-3

ABC|Test1|2018-08-02|349|12|6|6

XYZ|Test1|2018-08-02|68|31|38|-7

ABC|Test1|2018-08-03|349|12|0|12

XYZ|Test1|2018-08-03|68|12|0|12

ABC|Test1|2018-08-04|349|12|0|12

XYZ|Test1|2018-08-04|68|12|0|12

ABC|Test1|2018-08-16|349|34|30|4

Expected Result  -->

Proj|key|2018-08-01|2018-08-02|2018-08-03|2018-08-04|2018-08-16

---------------------------------------------------------------

ABC|Test_Cycle|Test1|Test1|Test1|Test1|Test1

       |In_Scope  |349|349|349|349|349

       |Expected_Value|12|12|12|12|34

       |Actual_Value|5|6|0|0|30

       |Difference  |-7|6|12|12|4

XYZ|Test_Cycle|Test1|Test1|Test1|Test1

       |In_Scope  |68|68|68|68

       |Expected_Value|31|31|12|12

       |Actual_Value|34|38|0|0

       |Difference  |-3|-7|12|12

I am not much aware about transposing in MySQL and reordering in such o/p. Can someone please help me with same. @MySQL DB

Comments

Post Details

Added on Aug 7 2018
4 comments
4,370 views