Excel pivot table Storing in SQL 2008 / Cross tabbed tables

Question

Let's say I have a table with X and Y values like:

      x-1  x-2   x-3 
 y-1  V=1  v=4   v=6       
 y-2  V=1  v=4   v=67
 y-3  V=2  v=0   v=9
 y-4  V=4  v=5   v=62

where the value for x-1, y-1 is 1 and so on.

I need to store all the x value field names, all the y value field names, and all values.

Answer 1

You could do something as simple as:

+-------------------+
|    RowName        |
+-------------------+
| +uid int          |
| name nvarchar(50) |
| position int      |
+-------------------+

+-------------------+
|    ColumnName     |
+-------------------+
| +uid int          |
| name nvarchar(50) |
| position int      |
+-------------------+

+-------------------+
|      Value        |
+-------------------+
| +row int          |
| +column int       |
| value  int        |
+-------------------+

Note that the '+' above denotes a primary key (so, row and column together in the Value table would make up the primary key).

This would enable you to quickly dump the row and column names into their respective tables (while also maintaining the position where the name should appear when recreating the layout) and would provide a quick way to store your data based upon the tabular layout.

This approach doesn't really have any inherent "failsafes," other than ensuring that you can't accidentally duplicate a row/column pair, but it's a quick and dirty way to store what you're trying to represent.