locked
Transpose data from columns to rows RRS feed

  • Question

  • User-998846012 posted

    Hi all,

    I've to fetch 600 records from MySql database,where I've MachineName,MachineValue1,MachineValue2,MachineValue3, . . . . . . ,MachineValue600.

    I've MachineName in table1 and MachineValue1,MachineValue2,MachineValue3, . . . . . . ,MachineValue600 in table2.I'm joining it by using 'Id' which is the common column in both table1 and table2.

     

    I'm able to display like this

     

    MachineName   MachineValue1   MachineValue2    MachineValue3   . . . . . .    MachineValue600

      Machine1               22.2                       23.3                     66.6                                        22.2

      Machine1              234.4                     444.4                   566.6                                      465.7

      Machine1               44.3                      344.4                   456.7                                      666.6

      Machine2               22.2                       23.3                     66.6                                        22.2

      Machine2              234.4                     444.4                   566.6                                      465.7

      Machine2               44.3                      344.4                   456.7                                      666.6

      Machine3               22.2                       23.3                     66.6                                        22.2

      Machine3              234.4                     444.4                   566.6                                      465.7

      Machine3               44.3                      344.4                   456.7                                      666.6

     

    My problem is Machine1 or Machine2 or ...... are repeated 3 times and I want to transpose the columns to rows.

     

    I want to display like this

     

    MachineValue1      Machine1      22.2      234.4      44.3

    MachineValue2      Machine2      23.3      444.4      566.6

    MachineValue3      Machine3      66.6      566.6      456.7

    .

    .

    .

    .

    .

    .

    MachineValue600      Machine600      600a      600b      600c

     

    Please help me.I'm trying to figure out the solution since january.I'm not getting the output as still learning .net.

    Tuesday, February 15, 2011 4:48 AM

Answers

  • User269602965 posted

    very good, so lets make some test tables to have some data.

    CREATE TABLE TABLE1 
    ( 
    MACHINE_ID  NUMBER(12), 
    MACHINENAME VARCHAR2(16) 
    ); 
     
    /* Test TABLE2 has 6 Columns */ 
     
    CREATE TABLE TABLE2 
    ( 
    MACHINE_ID      NUMBER(12), 
    MACHINEVALUE1   NUMBER(12,1), 
    MACHINEVALUE2   NUMBER(12,1), 
    MACHINEVALUE3   NUMBER(12,1), 
    MACHINEVALUE4   NUMBER(12,1), 
    MACHINEVALUE5   NUMBER(12,1)
    );
    
    INSERT INTO TABLE1
    VALUES (1, 'MACHINE1');
    INSERT INTO TABLE1
    VALUES (2, 'MACHINE2');
    INSERT INTO TABLE1
    VALUES (3, 'MACHINE3');
    INSERT INTO TABLE1
    VALUES (4, 'MACHINE4');
    INSERT INTO TABLE1
    VALUES (5, 'MACHINE5');
    COMMIT;
    
    INSERT INTO TABLE2
    VALUES (1, 12.5, 3.2, 6.7, 8.6, 100.2);
    INSERT INTO TABLE2
    VALUES (1, 16.7, 4.2, 7.7, 23.6, 56.3);
    INSERT INTO TABLE2
    VALUES (1, 56.7, 3.2, 54.4, 13.6, 27.5);
    INSERT INTO TABLE2
    VALUES (2, 24.2, 34.5, 8.1, 88.8, 100.6);
    INSERT INTO TABLE2
    VALUES (2, 56.3, 5.7, 5.3, 35.8, 79.5);
    INSERT INTO TABLE2
    VALUES (2, 55.7, 67.2, 34.4, 44.4, 39.8);
    INSERT INTO TABLE2
    VALUES (3, 84.5, 34.2, 4.5, 3.3, 81.4);
    INSERT INTO TABLE2
    VALUES (3, 3.7, 83.4, 97.8, 45.5, 39.3);
    INSERT INTO TABLE2
    VALUES (3, 34.7, 67.2, 42.4, 34.6, 89.5);
    INSERT INTO TABLE2
    VALUES (4, 9.5, 34.3, 15.9, 4.6, 98.2);
    INSERT INTO TABLE2
    VALUES (4, 18.7, 34.2, 56.7, 45.2, 4.5);
    INSERT INTO TABLE2
    VALUES (4, 45.7, 5.2, 7.4, 4.6, 98.4);
    INSERT INTO TABLE2
    VALUES (5, 16.4, 35.2, 34.7, 54.6, 99.2);
    INSERT INTO TABLE2
    VALUES (5, 19.7, 5.2, 23.6, 45.6, 87.3);
    INSERT INTO TABLE2
    VALUES (5, 12.7, 34.3, 43.4, 45.1, 34.5);
    
    COMMIT;
    
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 22, 2011 6:39 PM
  • User269602965 posted
    /*
    This is how you could do it in Oracle.
    
    First in the inner select
      you have to normalize the 5 machines, 
      each machine having 5 measurement parameters,
      each parameter measured 3 times
      for each unique machine parameter and machine ID (MACHINEVALUE_MACHINEID)
    Second, in the middle select
      using Oracles ROW_NUMBER() and PARTITIONING function
      you assign each of the 3 measurement sessions a unique number 1 through 3.
    Third, in teh outer select
      you pivot each of the measurement sessions to its own column.
    
    Your problem is MySQL is not a full featured database like Oracle.
    So some time will have to be spent determining what MySQL functions are available to you.
    I looked at their manual table of contents, and it looked pretty basic.
    */
    
    SELECT
      d.MACHINEVALUE_MACHINEID,
      MAX(DECODE(d.MACHINE_MEASUREMENT_NUMBER, 1, d.MACHINE_VALUE, NULL )) AS MEASURE_1,
      MAX(DECODE(d.MACHINE_MEASUREMENT_NUMBER, 2, d.MACHINE_VALUE, NULL )) AS MEASURE_2,
      MAX(DECODE(d.MACHINE_MEASUREMENT_NUMBER, 3, d.MACHINE_VALUE, NULL )) AS MEASURE_3
    FROM
      (
      SELECT
        c.MACHINEVALUE_MACHINEID,
        c.MACHINE_VALUE,
        ROW_NUMBER() OVER (PARTITION BY c.MACHINEVALUE_MACHINEID ORDER BY c.MACHINEVALUE_MACHINEID) AS MACHINE_MEASUREMENT_NUMBER
      FROM
        (
        SELECT
          'MACHINEVALUE1 '|| b.MACHINENAME  AS MACHINEVALUE_MACHINEID,
          a.MACHINEVALUE1 AS MACHINE_VALUE
        FROM TABLE2 a
        LEFT OUTER JOIN TABLE1 b ON a.MACHINE_ID = b.MACHINE_ID
        UNION ALL
        SELECT
          'MACHINEVALUE2 '|| b.MACHINENAME  AS MACHINEVALUE_MACHINEID,
          a.MACHINEVALUE2 AS MACHINE_VALUE
        FROM TABLE2 a
        LEFT OUTER JOIN TABLE1 b ON a.MACHINE_ID = b.MACHINE_ID
        UNION ALL
        SELECT
          'MACHINEVALUE3 '|| b.MACHINENAME  AS MACHINEVALUE_MACHINEID,
          a.MACHINEVALUE3 AS MACHINE_VALUE
        FROM TABLE2 a
        LEFT OUTER JOIN TABLE1 b ON a.MACHINE_ID = b.MACHINE_ID
        UNION ALL
        SELECT
          'MACHINEVALUE4 '|| b.MACHINENAME  AS MACHINEVALUE_MACHINEID,
          a.MACHINEVALUE4 AS MACHINE_VALUE
        FROM TABLE2 a
        LEFT OUTER JOIN TABLE1 b ON a.MACHINE_ID = b.MACHINE_ID
        UNION ALL
        SELECT
          'MACHINEVALUE5 '|| b.MACHINENAME  AS MACHINEVALUE_MACHINEID,
          a.MACHINEVALUE5 AS MACHINE_VALUE
        FROM TABLE2 a
        LEFT OUTER JOIN TABLE1 b ON a.MACHINE_ID = b.MACHINE_ID
        ) c
      ) d
    GROUP BY
      d.MACHINEVALUE_MACHINEID
    ORDER BY
      d.MACHINEVALUE_MACHINEID
    /  
    
    MACHINEVALUE_MACHINEID          MEASURE_1  MEASURE_2  MEASURE_3
    ------------------------------ ---------- ---------- ----------
    MACHINEVALUE1 MACHINE1               16.7       56.7       12.5
    MACHINEVALUE1 MACHINE2               55.7       56.3       24.2
    MACHINEVALUE1 MACHINE3               34.7        3.7       84.5
    MACHINEVALUE1 MACHINE4               45.7       18.7        9.5
    MACHINEVALUE1 MACHINE5               12.7       19.7       16.4
    MACHINEVALUE2 MACHINE1                4.2        3.2        3.2
    MACHINEVALUE2 MACHINE2                5.7       67.2       34.5
    MACHINEVALUE2 MACHINE3               67.2       83.4       34.2
    MACHINEVALUE2 MACHINE4               34.2        5.2       34.3
    MACHINEVALUE2 MACHINE5               34.3        5.2       35.2
    MACHINEVALUE3 MACHINE1               54.4        7.7        6.7
    MACHINEVALUE3 MACHINE2               34.4        5.3        8.1
    MACHINEVALUE3 MACHINE3               42.4       97.8        4.5
    MACHINEVALUE3 MACHINE4               56.7        7.4       15.9
    MACHINEVALUE3 MACHINE5               43.4       23.6       34.7
    MACHINEVALUE4 MACHINE1               13.6       23.6        8.6
    MACHINEVALUE4 MACHINE2               44.4       35.8       88.8
    MACHINEVALUE4 MACHINE3               34.6       45.5        3.3
    MACHINEVALUE4 MACHINE4               45.2        4.6        4.6
    MACHINEVALUE4 MACHINE5               45.6       45.1       54.6
    MACHINEVALUE5 MACHINE1               27.5       56.3      100.2
    MACHINEVALUE5 MACHINE2               79.5       39.8      100.6
    MACHINEVALUE5 MACHINE3               89.5       39.3       81.4
    MACHINEVALUE5 MACHINE4               98.4        4.5       98.2
    MACHINEVALUE5 MACHINE5               34.5       87.3       99.2
    
    25 rows selected.

    By normalize, I mean store the data as it was collected in the real world.

    You have a machine with 600 gauges.  You can read each gauge one or more times.  You have one or more machines, all with the same gauges.  So instead of collecting in like a spreadsheet with 600 columns like you have currently, in a normalized database there would be one row with four columns:  MachineID, GaugeID, MeasurementEventNumber, MeasurementValue.  In this manner, it would be a whole lot easier to roll up, do math, and pivot values.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 22, 2011 8:04 PM

All replies

  • User269602965 posted

    Does MySQL have the SQL PIVOT clause like Oracle?

    Does MySQL have the SQL DECCODE clause like Oracle?

    If so, I can show some examples of pivoting rows to columns in SQL statement. 

    Tuesday, February 15, 2011 6:27 PM
  • User269602965 posted

    Sorry the SQL clause is DECODE (not DECCODE) 

    Tuesday, February 15, 2011 6:28 PM
  • User-998846012 posted

    Thanks for your reply.Yes.I think they do..Is that only one way to solve the problem?I'm using join also,so I hope that, it doesnt bother much..Is there any other way to solve it.I just want to know all the ways.

    Tuesday, February 15, 2011 11:57 PM
  • User269602965 posted

    Yes, I see your join too.

    I am tied up at the moment, but I might have some time later today to look at a solution,

    unless another forum member has time sooner.

    Wednesday, February 16, 2011 11:08 AM
  • User269602965 posted

    Okay step one.

    Show the data definitions for table 1 and table 2.

    For example, just guessing table1 is

    Create Table TABLE1

    (

    MACHINE_ID   NUMBER(12),

    MACHINE_NAME  VARCHA2(32)

    )

    +++++++

    Also, are we to assume each named machine always has three sets of measurement observations??

    Wednesday, February 16, 2011 6:45 PM
  • User-998846012 posted
    Yes it has three sets of values.Machine Value 1 is header name (column header of table 1) and Machine1 is varchar (which is in table 2) and values are varchar.
    Saturday, February 19, 2011 1:08 AM
  • User269602965 posted

    So looking at all your notes, have I guessed your data table data definitions accurately??

     

    /* TABLE1 has 2 Columns */
    
    CREATE TABLE TABLE1
    (
    MACHINE_ID  NUMBER(12),
    MACHINENAME VARCHAR2(16)
    );
    
    /* TABLE2 has 601 Columns */
    
    CREATE TABLE TABLE2
    (
    MACHINE_ID      NUMBER(12),
    MACHINEVALUE1   NUMBER(12,1),
    MACHINEVALUE2   NUMBER(12,1),
    MACHINEVALUE3   NUMBER(12,1),
    MACHINEVALUE4   NUMBER(12,1),
    MACHINEVALUE5   NUMBER(12,1).
    ... 
    MACHINEVALUE600 NUMBER(12,1)
    );
    
    /* Table 1 is simply a lookup table for the         */
    /* machine_id and the name of the machine           */
    
    /* For each MACHINE_ID in TABLE2,                   */
    /* there are one or more rows of values,            */
    /* but not to exceed three rows for each MACHINE_ID */
    /* You want to PIVOT the three rows into columns    */
    
    Sunday, February 20, 2011 11:50 AM
  • User-998846012 posted

    Yes.You are right.This is what I need.

    Tuesday, February 22, 2011 12:44 AM
  • User269602965 posted

    very good, so lets make some test tables to have some data.

    CREATE TABLE TABLE1 
    ( 
    MACHINE_ID  NUMBER(12), 
    MACHINENAME VARCHAR2(16) 
    ); 
     
    /* Test TABLE2 has 6 Columns */ 
     
    CREATE TABLE TABLE2 
    ( 
    MACHINE_ID      NUMBER(12), 
    MACHINEVALUE1   NUMBER(12,1), 
    MACHINEVALUE2   NUMBER(12,1), 
    MACHINEVALUE3   NUMBER(12,1), 
    MACHINEVALUE4   NUMBER(12,1), 
    MACHINEVALUE5   NUMBER(12,1)
    );
    
    INSERT INTO TABLE1
    VALUES (1, 'MACHINE1');
    INSERT INTO TABLE1
    VALUES (2, 'MACHINE2');
    INSERT INTO TABLE1
    VALUES (3, 'MACHINE3');
    INSERT INTO TABLE1
    VALUES (4, 'MACHINE4');
    INSERT INTO TABLE1
    VALUES (5, 'MACHINE5');
    COMMIT;
    
    INSERT INTO TABLE2
    VALUES (1, 12.5, 3.2, 6.7, 8.6, 100.2);
    INSERT INTO TABLE2
    VALUES (1, 16.7, 4.2, 7.7, 23.6, 56.3);
    INSERT INTO TABLE2
    VALUES (1, 56.7, 3.2, 54.4, 13.6, 27.5);
    INSERT INTO TABLE2
    VALUES (2, 24.2, 34.5, 8.1, 88.8, 100.6);
    INSERT INTO TABLE2
    VALUES (2, 56.3, 5.7, 5.3, 35.8, 79.5);
    INSERT INTO TABLE2
    VALUES (2, 55.7, 67.2, 34.4, 44.4, 39.8);
    INSERT INTO TABLE2
    VALUES (3, 84.5, 34.2, 4.5, 3.3, 81.4);
    INSERT INTO TABLE2
    VALUES (3, 3.7, 83.4, 97.8, 45.5, 39.3);
    INSERT INTO TABLE2
    VALUES (3, 34.7, 67.2, 42.4, 34.6, 89.5);
    INSERT INTO TABLE2
    VALUES (4, 9.5, 34.3, 15.9, 4.6, 98.2);
    INSERT INTO TABLE2
    VALUES (4, 18.7, 34.2, 56.7, 45.2, 4.5);
    INSERT INTO TABLE2
    VALUES (4, 45.7, 5.2, 7.4, 4.6, 98.4);
    INSERT INTO TABLE2
    VALUES (5, 16.4, 35.2, 34.7, 54.6, 99.2);
    INSERT INTO TABLE2
    VALUES (5, 19.7, 5.2, 23.6, 45.6, 87.3);
    INSERT INTO TABLE2
    VALUES (5, 12.7, 34.3, 43.4, 45.1, 34.5);
    
    COMMIT;
    
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 22, 2011 6:39 PM
  • User269602965 posted
    /*
    This is how you could do it in Oracle.
    
    First in the inner select
      you have to normalize the 5 machines, 
      each machine having 5 measurement parameters,
      each parameter measured 3 times
      for each unique machine parameter and machine ID (MACHINEVALUE_MACHINEID)
    Second, in the middle select
      using Oracles ROW_NUMBER() and PARTITIONING function
      you assign each of the 3 measurement sessions a unique number 1 through 3.
    Third, in teh outer select
      you pivot each of the measurement sessions to its own column.
    
    Your problem is MySQL is not a full featured database like Oracle.
    So some time will have to be spent determining what MySQL functions are available to you.
    I looked at their manual table of contents, and it looked pretty basic.
    */
    
    SELECT
      d.MACHINEVALUE_MACHINEID,
      MAX(DECODE(d.MACHINE_MEASUREMENT_NUMBER, 1, d.MACHINE_VALUE, NULL )) AS MEASURE_1,
      MAX(DECODE(d.MACHINE_MEASUREMENT_NUMBER, 2, d.MACHINE_VALUE, NULL )) AS MEASURE_2,
      MAX(DECODE(d.MACHINE_MEASUREMENT_NUMBER, 3, d.MACHINE_VALUE, NULL )) AS MEASURE_3
    FROM
      (
      SELECT
        c.MACHINEVALUE_MACHINEID,
        c.MACHINE_VALUE,
        ROW_NUMBER() OVER (PARTITION BY c.MACHINEVALUE_MACHINEID ORDER BY c.MACHINEVALUE_MACHINEID) AS MACHINE_MEASUREMENT_NUMBER
      FROM
        (
        SELECT
          'MACHINEVALUE1 '|| b.MACHINENAME  AS MACHINEVALUE_MACHINEID,
          a.MACHINEVALUE1 AS MACHINE_VALUE
        FROM TABLE2 a
        LEFT OUTER JOIN TABLE1 b ON a.MACHINE_ID = b.MACHINE_ID
        UNION ALL
        SELECT
          'MACHINEVALUE2 '|| b.MACHINENAME  AS MACHINEVALUE_MACHINEID,
          a.MACHINEVALUE2 AS MACHINE_VALUE
        FROM TABLE2 a
        LEFT OUTER JOIN TABLE1 b ON a.MACHINE_ID = b.MACHINE_ID
        UNION ALL
        SELECT
          'MACHINEVALUE3 '|| b.MACHINENAME  AS MACHINEVALUE_MACHINEID,
          a.MACHINEVALUE3 AS MACHINE_VALUE
        FROM TABLE2 a
        LEFT OUTER JOIN TABLE1 b ON a.MACHINE_ID = b.MACHINE_ID
        UNION ALL
        SELECT
          'MACHINEVALUE4 '|| b.MACHINENAME  AS MACHINEVALUE_MACHINEID,
          a.MACHINEVALUE4 AS MACHINE_VALUE
        FROM TABLE2 a
        LEFT OUTER JOIN TABLE1 b ON a.MACHINE_ID = b.MACHINE_ID
        UNION ALL
        SELECT
          'MACHINEVALUE5 '|| b.MACHINENAME  AS MACHINEVALUE_MACHINEID,
          a.MACHINEVALUE5 AS MACHINE_VALUE
        FROM TABLE2 a
        LEFT OUTER JOIN TABLE1 b ON a.MACHINE_ID = b.MACHINE_ID
        ) c
      ) d
    GROUP BY
      d.MACHINEVALUE_MACHINEID
    ORDER BY
      d.MACHINEVALUE_MACHINEID
    /  
    
    MACHINEVALUE_MACHINEID          MEASURE_1  MEASURE_2  MEASURE_3
    ------------------------------ ---------- ---------- ----------
    MACHINEVALUE1 MACHINE1               16.7       56.7       12.5
    MACHINEVALUE1 MACHINE2               55.7       56.3       24.2
    MACHINEVALUE1 MACHINE3               34.7        3.7       84.5
    MACHINEVALUE1 MACHINE4               45.7       18.7        9.5
    MACHINEVALUE1 MACHINE5               12.7       19.7       16.4
    MACHINEVALUE2 MACHINE1                4.2        3.2        3.2
    MACHINEVALUE2 MACHINE2                5.7       67.2       34.5
    MACHINEVALUE2 MACHINE3               67.2       83.4       34.2
    MACHINEVALUE2 MACHINE4               34.2        5.2       34.3
    MACHINEVALUE2 MACHINE5               34.3        5.2       35.2
    MACHINEVALUE3 MACHINE1               54.4        7.7        6.7
    MACHINEVALUE3 MACHINE2               34.4        5.3        8.1
    MACHINEVALUE3 MACHINE3               42.4       97.8        4.5
    MACHINEVALUE3 MACHINE4               56.7        7.4       15.9
    MACHINEVALUE3 MACHINE5               43.4       23.6       34.7
    MACHINEVALUE4 MACHINE1               13.6       23.6        8.6
    MACHINEVALUE4 MACHINE2               44.4       35.8       88.8
    MACHINEVALUE4 MACHINE3               34.6       45.5        3.3
    MACHINEVALUE4 MACHINE4               45.2        4.6        4.6
    MACHINEVALUE4 MACHINE5               45.6       45.1       54.6
    MACHINEVALUE5 MACHINE1               27.5       56.3      100.2
    MACHINEVALUE5 MACHINE2               79.5       39.8      100.6
    MACHINEVALUE5 MACHINE3               89.5       39.3       81.4
    MACHINEVALUE5 MACHINE4               98.4        4.5       98.2
    MACHINEVALUE5 MACHINE5               34.5       87.3       99.2
    
    25 rows selected.

    By normalize, I mean store the data as it was collected in the real world.

    You have a machine with 600 gauges.  You can read each gauge one or more times.  You have one or more machines, all with the same gauges.  So instead of collecting in like a spreadsheet with 600 columns like you have currently, in a normalized database there would be one row with four columns:  MachineID, GaugeID, MeasurementEventNumber, MeasurementValue.  In this manner, it would be a whole lot easier to roll up, do math, and pivot values.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 22, 2011 8:04 PM