0

I have got a vast stock data in mysql in the following format... 

  date      symbol    open   high   low    close  volume 
2017-11-07  stockA    8.9    8.92   8.88   8.9     68987
2017-11-08  stockA    8.91   8.95   8.9    8.92    98793
2017-11-09  stockA    8.75   8.88   8.71   8.77    99871
2017-11-07  stockB    18.9   18.92  18.88  18.9    12109
2017-11-08  stockB    18.91  18.95  18.9   18.92   11898
2017-11-09  stockB    18.75  18.88  18.71  18.77   10898
2017-11-07  stockC    3.9    3.92    3.88   3.9    98093
2017-11-08  stockC    3.91   3.95    3.9    3.92  109871 
2017-11-09  stockC    3.75   3.88    3.71   3.77  139405 
...

I am looking to create a table that gathers the types of prices as rows and of all stocks in the database as columns: 

     date      symbol    open   high   low    close  volume 
    2017-11-07  stockA    8.9    8.92   8.88   8.9     68987
    2017-11-08  stockA    8.91   8.95   8.9    8.92    98793
    2017-11-09  stockA    8.75   8.88   8.71   8.77    99871
    
    2017-11-07  stockB    18.9   18.92  18.88  18.9    12109
    2017-11-08  stockB    18.91  18.95  18.9   18.92   11898
    2017-11-09  stockB    18.75  18.88  18.71  18.77   10898
    
    2017-11-07  stockC    3.9    3.92    3.88   3.9    98093
    2017-11-08  stockC    3.91   3.95    3.9    3.92  109871 
    2017-11-09  stockC    3.75   3.88    3.71   3.77  139405 
    ...
date          type    stockA    stockB   stockC stockD ...
 2017-11-07   open      8.9     18.9     3.9     
 2017-11-08   open      8.92    18.92    3.92
 2017-11-09   open      8.77    18.77    3.77
 2017-11-07   high      8.9     18.92    3.92     
 2017-11-08   high      8.95    18.95    3.95
 2017-11-09   high      8.88    18.88    3.88
 2017-11-07   low       8.88    18.88    3.88     
 2017-11-08   low       8.9     18.9     3.9 
 2017-11-09   low       8.71    18.71    3.71
 2017-11-07   close     8.9     18.9     3.9     
 2017-11-08   close     8.92    18.92    3.92 
 2017-11-09   close     8.77    18.77    3.77

I'm a newbie to mysql/ sql syntax... so i'm totally clueless about how i could go about to do this... has anyone done anything similar?

thanks all for the links etc.. I tried to tackle the problem with the following script after reading the links... somehow it threw a SQL syntax error (error code:1064). It looks like the script stopped after sorting for the first 2 stocks, then there was a mismatch of symbol... 

SET @sql = NULL;
SELECT 
  GROUP_CONCAT( DISTINCT 
      CONCAT(
        'MAX( if( symbol="',
          symbol,
          '" , open, NULL )) as "',
           symbol, '"' 
         )
      ) INTO @sql
FROM prices ;

SET @sql = CONCAT(' SELECT date, ', @sql, '
          FROM prices');
PREPARE stmt from @sql ;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Rick James
  • 135,179
  • 13
  • 127
  • 222
Ben Tse
  • 1
  • 1
  • 6
    Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) – CBroe Nov 09 '17 at 15:50
  • Read here about the pivot technique: http://modern-sql.com/use-case/pivot – Markus Winand Nov 09 '17 at 15:57
  • I've edited the desired result to show that the problem is more or transposing rows and columns whilst grouping data by date... the database currently has over 10 years of daily data and excess of 2000 symbols – Ben Tse Nov 10 '17 at 02:22
  • Databases are not designed to store data in that format or wrangling. Consider importing into dataset applications like Python pandas, R, SAS to reshape easily! – Parfait Nov 10 '17 at 02:51
  • The "grouping by day" is still part of "pivot". Please do read the links given, However are you expecting 2000 columns? You might not like the outcome. – Paul Maxwell Nov 10 '17 at 02:55
  • thanks... i've tried to come up with a script to tackle the problem after reading the links... pls see above what i could come up with at this point... the script however threw an SQL syntax error, can someone please help to point out when went wrong? – Ben Tse Nov 10 '17 at 03:39
  • @Parfait thanks for the suggestion. I did try using R to tackle the issue, but perhaps the database is too big, it crashes all the time – Ben Tse Nov 10 '17 at 03:41

0 Answers0