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

798542

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

Gaz in Oz

...it would help if you supplied CREATE TABLE and INSERT statements for the data, so writing any sql can use your table.

798542

Please find create table and insert statements as follows-

create table t1 (Proj varchar(250) , Test_Cycle varchar(13) , eDate date, In_Scope bigint(21),Expected_Value int(100) ,Actual_Value int(100) , Diff bigint(67));

insert into t1 values( 'ABC','Test1','2018-08-01',349,12,5,7);

insert into t1 values( 'XYZ','Test1','2018-08-01',68,31,34,-3);

insert into t1 values( 'ABC','Test1','2018-08-02',349,12,6,6);

insert into t1 values( 'XYZ','Test1','2018-08-02',68,31,38,-7);

insert into t1 values( 'ABC','Test1','2018-08-03',349,12,0,12);

insert into t1 values( 'XYZ','Test1','2018-08-03',68,12,0,12);

insert into t1 values( 'ABC','Test1','2018-08-04',349,12,0,12);

insert into t1 values( 'XYZ','Test1','2018-08-04',68,12,0,12);

insert into t1 values( 'ABC','Test1','2018-08-16',349,34,30,4);

Dave Stokes-MySQL Community Team-Oracle

I think you are asking how to create a pivot table.  There are many examples out there like http://codingsight.com/pivot-tables-in-mysql/

Barbara Boehmer

The following pivots the data and I think is fairly close to your desired output. 

On my computer, the columns are aligned, but I can't seem to find a way to align them on this forum.

mysql> select * from t1;
+------+------------+------------+----------+----------------+--------------+------+
| 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 |
+------+------------+------------+----------+----------------+--------------+------+
9 rows in set (0.00 sec)

mysql> select t.*
    -> from   (select proj, test_cycle,
    ->                'In_Scope' AS the_key,
    ->                MAX(IF(eDate = '2018-08-01', In_Scope, NULL)) AS '2018-08-01',
    ->                MAX(IF(eDate = '2018-08-02', In_Scope, NULL)) AS '2018-08-02',
    ->                MAX(IF(eDate = '2018-08-03', In_Scope, NULL)) AS '2018-08-03',
    ->                MAX(IF(eDate = '2018-08-04', In_Scope, NULL)) AS '2018-08-04',
    ->                MAX(IF(eDate = '2018-08-16', In_Scope, NULL)) AS '2018-08-16'
    ->         from   t1
    ->         group  by proj, test_cycle
    ->         union all
    ->         select proj, test_cycle,
    ->                'Expected_Value' AS the_key,
    ->                MAX(IF(eDate = '2018-08-01', Expected_Value, NULL)) AS '2018-08-01',
    ->                MAX(IF(eDate = '2018-08-02', Expected_Value, NULL)) AS '2018-08-02',
    ->                MAX(IF(eDate = '2018-08-03', Expected_Value, NULL)) AS '2018-08-03',
    ->                MAX(IF(eDate = '2018-08-04', Expected_Value, NULL)) AS '2018-08-04',
    ->                MAX(IF(eDate = '2018-08-16', Expected_Value, NULL)) AS '2018-08-16'
    ->         from   t1
    ->         group  by proj, test_cycle
    ->         union all
    ->         select proj, test_cycle,
    ->                'Actual_Value' AS the_key,
    ->                MAX(IF(eDate = '2018-08-01', Actual_Value, NULL)) AS '2018-08-01',
    ->                MAX(IF(eDate = '2018-08-02', Actual_Value, NULL)) AS '2018-08-02',
    ->                MAX(IF(eDate = '2018-08-03', Actual_Value, NULL)) AS '2018-08-03',
    ->                MAX(IF(eDate = '2018-08-04', Actual_Value, NULL)) AS '2018-08-04',
    ->                MAX(IF(eDate = '2018-08-16', Actual_Value, NULL)) AS '2018-08-16'
    ->         from   t1
    ->         group  by proj, test_cycle
    ->         union all
    ->         select proj, test_cycle,
    ->                'Difference' AS the_key,
    ->                MAX(IF(eDate = '2018-08-01', Diff, NULL)) AS '2018-08-01',
    ->                MAX(IF(eDate = '2018-08-02', Diff, NULL)) AS '2018-08-02',
    ->                MAX(IF(eDate = '2018-08-03', Diff, NULL)) AS '2018-08-03',
    ->                MAX(IF(eDate = '2018-08-04', Diff, NULL)) AS '2018-08-04',
    ->                MAX(IF(eDate = '2018-08-16', Diff, NULL)) AS '2018-08-16'
    ->         from   t1
    ->         group  by proj, test_cycle) t
    -> order  by t.proj, t.test_cycle,
    ->        case
    ->          when t.the_key = 'In_Scope'       then 1
    ->          when t.the_key = 'Expected_Value' then 2
    ->          when t.the_key = 'Actual_Value'   then 3
    ->          when t.the_key = 'Difference'     then 4
    ->        end;
+------+------------+----------------+------------+------------+------------+------------+------------+
| proj | test_cycle | the_key        | 2018-08-01 | 2018-08-02 | 2018-08-03 | 2018-08-04 | 2018-08-16 |
+------+------------+----------------+------------+------------+------------+------------+------------+
| ABC  | Test1      | In_Scope       |        349 |        349 |        349 |        349 |        349 |
| ABC  | Test1      | Expected_Value |         12 |         12 |         12 |         12 |         34 |
| ABC  | Test1      | Actual_Value   |          5 |          6 |          0 |          0 |         30 |
| ABC  | Test1      | Difference     |          7 |          6 |         12 |         12 |          4 |
| XYZ  | Test1      | In_Scope       |         68 |         68 |         68 |         68 |       NULL |
| XYZ  | Test1      | Expected_Value |         31 |         31 |         12 |         12 |       NULL |
| XYZ  | Test1      | Actual_Value   |         34 |         38 |          0 |          0 |       NULL |
| XYZ  | Test1      | Difference     |         -3 |         -7 |         12 |         12 |       NULL |
+------+------------+----------------+------------+------------+------------+------------+------------+
8 rows in set (0.02 sec)

MySQL>