what is the recommended way to save a M by N matrix in sql table

Question

If I have a M by N matrix filled with data, what is the best way to save it to a sql table for fast query? I can save a 3 column table (X, Y,Z) total MN rows, or simply M rows with N columns. N is in the order of hundreds to thousands, M can be much larger. I guess the second way is faster for query, since the query can be build on known column names. But would it recommended to have as many columns in a table? thanks,

Answer 1

Your second option (N columns) will make it harder to select values from column unknown in compile time (you'll have to dynamicaly generate selects). So I'll go with table (X,Y,value) and make (x,y) a primary key.