Forum Stats

  • 3,740,456 Users
  • 2,248,256 Discussions
  • 7,861,252 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Log In Register

Categories

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

798542
798542 Member Posts: 10
edited Aug 16, 2018 12:38AM in MySQL Community Space

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

Tagged:
Barbara Boehmer
· Share on TwitterShare on Facebook

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,777 Bronze Crown
    edited Aug 7, 2018 4:25AM

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

    · Share on TwitterShare on Facebook
  • 798542
    798542 Member Posts: 10
    edited Aug 7, 2018 9:58AM

    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);

    · Share on TwitterShare on Facebook
  • Dave Stokes-MySQL Community Team-Oracle
    Dave Stokes-MySQL Community Team-Oracle MySQL Community Manager TexasMember Posts: 354 Employee
    edited Aug 7, 2018 10:54AM

    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
    · Share on TwitterShare on Facebook
  • Barbara Boehmer
    Barbara Boehmer Member Posts: 4,625
    edited Aug 16, 2018 12:38AM

    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>

    · Share on TwitterShare on Facebook
Sign In or Register to comment.