0

Having as always a very hard time to join tables...

I have two tables, the first one is stocks.

+----+------------+--------+----------+----------+
| id |    name    | symbol |  ticker  | currency |
+----+------------+--------+----------+----------+
|  1 | Swedban    | swedb  | swedb.st | sek      |
|  2 | ABB        | abb    | abb.st   | sek      |
|  3 | Alfa Laval | alfa   | alfa.st  | sek      |
+----+------------+--------+----------+----------+

And the second one is prices

+----+----------+---------------------+------+-----+------+-------+-----------+--------+------------+
| id | stock_id |        ydate        | open | low | high | close | adj_close | volume | created_at |
+----+----------+---------------------+------+-----+------+-------+-----------+--------+------------+
|  1 |        1 | 2020-01-02 00:00:00 |  115 | 110 |  116 |   116 |       116 |    354 | 2021-01-03 |
|  2 |        2 | 2020-01-02 00:00:00 |   12 |  13 |   13 |    12 |        12 |    345 | 2021-01-03 |
|  3 |        3 | 2020-01-02 00:00:00 |   44 |  45 |   45 |    45 |        45 |  46456 | 2021-01-03 |
|  4 |        1 | 2020-01-03 00:00:00 |  110 | 111 |  111 |   111 |       111 | 678678 | 2021-01-03 |
|  5 |        2 | 2020-01-03 00:00:00 |   11 |  15 |   13 |    13 |        13 |  12313 | 2021-01-03 |
|  6 |        3 | 2020-01-03 00:00:00 |   40 |  44 |   43 |    43 |        43 |  13412 | 2021-01-03 |
|  7 |        1 | 2020-01-04 00:00:00 |  109 | 120 |  119 |   119 |       119 |    464 | 2021-01-03 |
|  8 |        2 | 2020-01-04 00:00:00 |   15 |  16 |   16 |    16 |        16 | 234243 | 2021-01-03 |
|  9 |        3 | 2020-01-04 00:00:00 |   43 |  43 |   43 |    43 |        43 |   1234 | 2021-01-03 |
+----+----------+---------------------+------+-----+------+-------+-----------+--------+------------+

The expected result should be:

+---+---------------------+---------+-----+------------+
|   |        ydate        | Swedban | ABB | Alfa Lavel |
+---+---------------------+---------+-----+------------+
| 1 | 2020-01-02 00:00:00 |     116 |  12 |         45 |
| 2 | 2020-01-03 00:00:00 |     111 |  13 |         43 |
| 3 | 2020-01-04 00:00:00 |     119 |  16 |         43 |
+---+---------------------+---------+-----+------------+

So stock names should be columns. And the adj_close and ydate rows.

Is this possible?

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Adam
  • 1,231
  • 1
  • 13
  • 37
  • Yes, what have you tried? in SQL you could use piviot tables, not sure of syntax in MySQL but you can look into that and post some code examples once you get them and we can help more. – Brad Jan 04 '21 at 21:14
  • you need to use pivot and most probably dynamic sql to get the list of company names – eshirvana Jan 04 '21 at 21:17
  • This operation is called pivoting and has been asked and answerered here on SO sooooo many times! The duplicate question shows how to perform a pivot with a dynamic number of columns using mysql only. However, pls note that it may be a lot more efficient to perform such a transformation in the application code rather than in mysql! – Shadow Jan 04 '21 at 21:24

0 Answers0