0

I have a table of data in excel that looks like this:

a busy cat

I need to get this data imported into an SQL table.

Eventually I need to be able to query the table by saying something like:

Select the value where x-axis equals 762 and y-axis equals 889 - this should return 30.60.

What is the best way to represent this data in an SQL table to make it easy to query?

GMB
  • 216,147
  • 25
  • 84
  • 135
Ray Lawlor
  • 368
  • 1
  • 4
  • 13
  • Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. PS We cannot tell anyone what is "best" in an engineering situation unless they define it in enough detail & give enough relevant details that everyone would agree on a valuation. Then you'd need to explain why you can't evaluate that yourself. – philipxy Jul 15 '20 at 01:27
  • Right now you are just asking for us to rewrite your textbook with a bespoke tutorial & do your (home)work & you have shown no research or other effort. Please see [ask], hits googling 'stackexchange homework' & the voting arrow mouseover texts. Show the steps of your work following your textbook with justification & ask 1 specific researched non-duplicate question re the first place you are stuck. Quote the definitions, theorems & algorithms you are relying on. All the steps are also SO faqs. – philipxy Jul 15 '20 at 01:28

1 Answers1

2

What is the best way to represent this data in an SQL table to make it easy to query?

The answer quite lies in your pseudo code. The most relevant way to store your data is in a table with three columns: x and y, and value, and a unique constraint on the first two columns.

the dataset provided in your example would translate as follows:

   x    y  value
----------------
 762  635  26.23
 762  762  28.41
 762  889  30.60
1016  635  27.61
1016  762  29.91
1016  889  32.31
1270  635  29.06
1270  762  31.48
1270  889  33.91

Select the value where x-axis equals 762 and y-axis equals 889 - this should return 30.60.

With this table structure, that's a simple query now:

select value from mytable where x = 762 and y = 889
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Many thanks for this. I knew this was probably the solution. My problem is, the question above featured only a tiny table for example only. In reality, I have 35 x-y tables with 500 rows and columns each. Listing them as you've suggested is correct, obviously, but will take probably 500! x 35 rows of SQL to represent and so I was hoping there'd be a more efficient way of handling this type of data. – Ray Lawlor Oct 23 '19 at 07:42