12 Replies Latest reply on Aug 5, 2019 12:52 PM by BluShadow

    How to "transpose" the table

    PAN KEVIN

      I hvae a table named DB_TEST, and want to "transpose" this table, I do not how ? any help is greatly appreciated.

       

      Here is my table DB_TEST.

       

       

      1. SITENAME SDATE SYEAR SMONTH SDAY SHOUR SMIN SDOYS STECVDATA 
      2. hlar 1999/3/1         1999 3 1 0 0 60 24.5288956021722 
      3. xiam 1999/3/1         1999 3 1 0 0 60 24.3713154564964 
      4. xiag 1999/3/1         1999 3 1 0 0 60 15.0201563915542 
      5. xiaa 1999/3/1         1999 3 1 0 0 60 26.0542207040388 
      6. wush 1999/3/1         1999 3 1 0 0 60 2.71761248884711 
      7. hlar 1999/3/1 0:15:00 1999 3 1 0 15 60 24.2014704297884 
      8. xiam 1999/3/1 0:15:00 1999 3 1 0 15 60 21.4712975425524 
      9. xiag 1999/3/1 0:15:00 1999 3 1 0 15 60 13.4331590043865 
      10. xiaa 1999/3/1 0:15:00 1999 3 1 0 15 60 20.3767699785842 
      11. wush 1999/3/1 0:15:00 1999 3 1 0 15 60 1.95670784601915 
      12. hlar 1999/3/1 0:30:00 1999 3 1 0 30 60 24.6487379379271 
      13. xiaa 1999/3/1 0:30:00 1999 3 1 0 30 60 23.1904423850339 

      The expected results is :

       

       

       

      1. SDATE            SYEAR     SMONTH SDAY SHOUR SMIN      SDOYS      hlar                xiam                xiag                     xiaa                wush 
      2. 1999/3/1         1999      3      1      0      0      60      24.5288956021722      24.3713154564964      15.0201563915542      26.0542207040388      2.71761248884711 
      3. 1999/3/1 0:15:00 1999      3      1      0      15      60      24.2014704297884      21.4712975425524      13.4331590043865      20.3767699785842      1.95670784601915 
      4. 1999/3/1 0:30:00 1999      3      1      0      30      60      24.6487379379271      NULL                NULL                     23.1904423850339      NULL 

      The points are:1. In the above example table, it have 5 sites, but I do not know how many sites in my real table.2. If there no data  with the corresponding SDATE and SITENAME, set NULL. Here is the table DB_TEST

       

       

      1. create table DB_TEST 
      2.   ssitename VARCHAR2(100), 
      3.   sdate     DATE
      4.   syear     NUMBER, 
      5.   smonth    NUMBER, 
      6.   sday      NUMBER, 
      7.   shour     NUMBER, 
      8.   smin      NUMBER, 
      9.   sdoys     NUMBER, 
      10.   stecvdata NUMBER 
      11.  
      12.  
      13. insert into DB_TEST (SSITENAME, SDATE, SYEAR, SMONTH, SDAY, SHOUR, SMIN, SDOYS, STECVDATA) 
      14. values ('hlar', to_date('01-03-1999', 'dd-mm-yyyy'), 1999, 3, 1, 0, 0, 60, 24.5288956021722); 
      15.  
      16.  
      17. insert into DB_TEST (SSITENAME, SDATE, SYEAR, SMONTH, SDAY, SHOUR, SMIN, SDOYS, STECVDATA) 
      18. values ('xiam', to_date('01-03-1999', 'dd-mm-yyyy'), 1999, 3, 1, 0, 0, 60, 24.3713154564964); 
      19.  
      20.  
      21. insert into DB_TEST (SSITENAME, SDATE, SYEAR, SMONTH, SDAY, SHOUR, SMIN, SDOYS, STECVDATA) 
      22. values ('xiag', to_date('01-03-1999', 'dd-mm-yyyy'), 1999, 3, 1, 0, 0, 60, 15.0201563915542); 
      23.  
      24.  
      25. insert into DB_TEST (SSITENAME, SDATE, SYEAR, SMONTH, SDAY, SHOUR, SMIN, SDOYS, STECVDATA) 
      26. values ('xiaa', to_date('01-03-1999', 'dd-mm-yyyy'), 1999, 3, 1, 0, 0, 60, 26.0542207040388); 
      27.  
      28.  
      29. insert into DB_TEST (SSITENAME, SDATE, SYEAR, SMONTH, SDAY, SHOUR, SMIN, SDOYS, STECVDATA) 
      30. values ('wush', to_date('01-03-1999', 'dd-mm-yyyy'), 1999, 3, 1, 0, 0, 60, 2.71761248884711); 
      31.  
      32.  
      33. insert into DB_TEST (SSITENAME, SDATE, SYEAR, SMONTH, SDAY, SHOUR, SMIN, SDOYS, STECVDATA) 
      34. values ('hlar', to_date('01-03-1999 00:15:00', 'dd-mm-yyyy hh24:mi:ss'), 1999, 3, 1, 0, 15, 60, 24.2014704297884); 
      35.  
      36.  
      37. insert into DB_TEST (SSITENAME, SDATE, SYEAR, SMONTH, SDAY, SHOUR, SMIN, SDOYS, STECVDATA) 
      38. values ('xiam', to_date('01-03-1999 00:15:00', 'dd-mm-yyyy hh24:mi:ss'), 1999, 3, 1, 0, 15, 60, 21.4712975425524); 
      39.  
      40.  
      41. insert into DB_TEST (SSITENAME, SDATE, SYEAR, SMONTH, SDAY, SHOUR, SMIN, SDOYS, STECVDATA) 
      42. values ('xiag', to_date('01-03-1999 00:15:00', 'dd-mm-yyyy hh24:mi:ss'), 1999, 3, 1, 0, 15, 60, 13.4331590043865); 
      43.  
      44.  
      45. insert into DB_TEST (SSITENAME, SDATE, SYEAR, SMONTH, SDAY, SHOUR, SMIN, SDOYS, STECVDATA) 
      46. values ('xiaa', to_date('01-03-1999 00:15:00', 'dd-mm-yyyy hh24:mi:ss'), 1999, 3, 1, 0, 15, 60, 20.3767699785842); 
      47.  
      48.  
      49. insert into DB_TEST (SSITENAME, SDATE, SYEAR, SMONTH, SDAY, SHOUR, SMIN, SDOYS, STECVDATA) 
      50. values ('wush', to_date('01-03-1999 00:15:00', 'dd-mm-yyyy hh24:mi:ss'), 1999, 3, 1, 0, 15, 60, 1.95670784601915); 
      51.  
      52.  
      53. insert into DB_TEST (SSITENAME, SDATE, SYEAR, SMONTH, SDAY, SHOUR, SMIN, SDOYS, STECVDATA) 
      54. values ('hlar', to_date('01-03-1999 00:30:00', 'dd-mm-yyyy hh24:mi:ss'), 1999, 3, 1, 0, 30, 60, 24.6487379379271); 
      55.  
      56.  
      57. insert into DB_TEST (SSITENAME, SDATE, SYEAR, SMONTH, SDAY, SHOUR, SMIN, SDOYS, STECVDATA) 
      58. values ('xiaa', to_date('01-03-1999 00:30:00', 'dd-mm-yyyy hh24:mi:ss'), 1999, 3, 1, 0, 30, 60, 23.1904423850339);