0

I was working with a 2D data in excel and wanted to store the data to use for my need in a MySQL Database. But I came to face a problem that might have a better solution which i might be missing. A sample example is given.

+-----+-----+-----+-----+
|     |  A  |  B  |  C  |
|-----|-----|-----|-----|
|  A  |  1  |  2  |  3  |
|-----|-----|-----|-----|
|  B  |  4  |  5  |  6  |
|-----|-----|-----|-----|
|  C  |  7  |  8  |  9  |
+-----+-----+-----+-----+

So, what I need is a way to query and get my value based on 2 values. So if I need value of AB it's 2 same as BC=6 and AA=1. I have this data in my excel I stored them as below in MySQL DB.

table:
+-------+------+-------+
|   v1  |  v2  |  sol  |
|-------|------|-------|
|   A   |   A  |   1   |
|-------|------|-------|
|   A   |   B  |   2   |
|-------|------|-------|
|   A   |   C  |   3   |
+-------+------+-------+
|   B   |   A  |   4   |
|-------|------|-------|
|   B   |   B  |   5   |
|-------|------|-------|
|   B   |   C  |   6   |
+-------+------+-------+
|   C   |   A  |   7   |
|-------|------|-------|
|   C   |   B  |   8   |
|-------|------|-------|
|   C   |   C  |   9   |
+-------+------+-------+

This way I can query SELECT sol FROM table WHERE v1 = 'A' AND v2 = 'C' and i will be getting the proper value AC = 3. But my worry is this excel will keep growing both in column and rows and I will have to make ((NxN - current rowcount) if I add 'D' then (4*4)-9= 7 new rows) 'N' number of rows in the table each time.

That's why I was thinking if there was a better way and if there is a better database except MySQL for my problem. Both answers will be very much appreciated. Thanks.

Shadow
  • 33,525
  • 10
  • 51
  • 64
arshad92
  • 43
  • 8

1 Answers1

0

In MySQL, you can use union all to unpivot this dataset. Assuming that the first column of your original table is called id, you would go:

select id v1, 'A' v2, a sol from mytable
union all select id, 'B', b from mytable
union all select id, 'C', c from mytable

Note: this will not scale well (each subquery implies a full scan of the table). You would probably be better off storing your data in the correct data structure from the start.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • Yes this helps in a way, now i can make a table with the same data in just 3 rows instead of 9 (`ID`, `name`, `A`, `B`, `C`) (1, 'A', '1', '2', '3'), (2, 'B', '4', '5', '6'), (3, 'C', '7', '8', '9'). But it also will cost me like big query time, big query results and also i will need to change query in my application backend each time I add a new column. Query: `select name, 'A' col, a value from mytable union all select name, 'B' col, b value from mytable union all select name, 'C' col, c value from mytable;` – arshad92 Sep 07 '20 at 23:38