Traverse SQL columns to build EAV table for refernce

I have a couple of spreadsheets which are used as reference tables in a spreadsheet system and which I am busy taking to SQL. I have imported the spreadsheets into SQL and would like to build a sort of EAV (Entity Attribute Value) table from the imported tables. I know it is not the most efficient way but is good enough for now.

WHERE_COLUMN    COL1    COL2     COL3    COL4
ROW1            2.00    0.00     4.00   10.00
ROW2            2.00    5.00     2.00    2.00
ROW3            2.00    2.00     0.00    2.00
ROW4            2.00    0.00     0.00    0.00
ROW5            2.00    2.00    10.00   80.00
ROW6            2.00    0.00     5.00    0.00

has to become:

ROW1    COL1    2.00
ROW1    COL2    0.00开发者_运维知识库
ROW1    COL3    4.00
ROW1    COL4   10.00

....

ROW6    COL3    5.00
ROW6    COL4    0.00

....

ROW(n)  COL(n)  0.00

Since the tables are quite large and has multiple variable amount of columns (some up to 50) it would be tedious to do so by hand. The amount of rows and columns per table may change over time and would be easier to INSERT / UPDATE then. Any ideas would be welcome or perhaps a better and cleaner method is also welcome. Thanks

If I stick to your column row model I'd use a table-layout like so:

**table sheetvalues**  
id: integer auto_increment primary key
sheet_id: integer foreign key to table sheet
rownr: integer not null indexed
colnr: integer not null indexed
value: integer (or whatever)

**table sheet**  
id: integer auto_increment primary key
sheetname: varchar
rowcount: integer
colcount: integer

You can import the table by building a dynamic SQL statement in a language like C#, php, perl, Java or whatever.
I'm going to use Delphi pseudo code.

function GetConversionSQL(DBName, Sheetname, SheetDef, DataTable: string): string;
var
  DefQuery: TQuery;
  Rowcount: integer;
  ColCount: integer;
  i: integer;
  Select: string;
begin
  DefQuery:= TQuery.Create(Form1);
  try
    DefQuery.DatabaseName:= DBName;
    DefQuery.SQL.Text:= 'SELECT rowcount, colcount FROM '+SheetDef+
                        'WHERE sheetname = ' + sheetname; 
    DefQuery.Open;
    //DefQuery will open on the first and only row
    RowCount:= DefQuery.FieldByName('Rowcount').AsInteger;
    ColCount:= DefQuery.FieldByName('Colcount').AsInteger;
  finally
    DefQuery.Free;
  end;

  Select:= '';
  if colcount > 0 then begin
    for i:= 1 to colcount do begin
      Select:= Select + 'SELECT where_col, col'+IntToStr(i)+' FROM '+DataTable;
      if i < colcount then Select:= Select + ' UNION ';
    end;
    Select:= 'INSERT INTO sheetvalues '+ Select;
  end;
  Result:= Select;
end;

procedure ConvertATable;
var
  ConversionSQL: string;
  ImportQuery: TQuery;
begin
  ConversionSQL:= GetConversionSQL('Mydatabase', 'sheet1', 'sheetdef'
                                   , 'table_sheet1');
  ImportQuery:= TQuery.Create(Form1);
  try
    ImportQuery.DatabaseName:= 'MyDatabase';
    ImportQuery.SQL.Text:= ConversionSQL;
    ImportQuery.ExecSQL;
  finally
    ImportQuery.Free; 
  end;
end;