Converting Vertical DB Table to Horizontal Table

[mcquiggd]

I have a database which I am migrating to a more useable structure, and one of the problems I have is that it was designed to be totally generic - performance and maintainability is appalling.

The structure of one table is totally vertical:

InstanceID int, Fieldname VarChar (255), FieldValue VarChar (255)

InstanceID is non-unique, The FieldValue can be anything, from a name to an email address.

For each InstanceID I need to extract the list of fields, and their values, and copy them into a new table structure (which I have already created programatically). The list of fields is different for each InstanceID...


Any suggestions...?

 

[richard-af]

What's the new structure? And is this Oracle?

 

[mcquiggd]

SQL Server 2005...

The architecture for want of a better description is one where 'Virtual Tables' are defined in the database and some in-house generic sql generator (the calls to which are hard coded which totally defeats the object!) - I have extracted this meta data and created physical, horizontal tables...

Basically it's now a case of migrating the data into these new tables - I am thinking of creating Views on the vertical table and attempting to extract the data that way, but its the 'rotation' of a vertical table to a horizontal table that is a problem...

 

[richard-af]

How about an example of the old table's data and same for new.

 

[Cowboy Bob]

Ahhh, the Inner Platform Effect.

I've seen these a few times, and they were always broken beyond repair. I have pity on you...

 

[scotspine]

i have some code

 

[richard-af]

i have some code

... and plenty cheapness.

 

[scotspine]

public DataTable TurnTable(DataTable source)
{
DataTable dest = new DataTable("Turned" + source.TableName);

dest.Columns.Add(" ");

foreach (DataRow r in source.Rows)
dest.Columns.Add(r[0].ToString()); // assign each row the plan name (r[0])


for (int i = 0; i < source.Columns.Count - 1; i++)
{
dest.Rows.Add(dest.NewRow());
}

for (int r = 0; r < dest.Rows.Count; r++)
{
for (int c = 0; c < dest.Columns.Count; c++)
{
if (c == 0)
{
dest.Rows[r][0] = source.Columns[r + 1].ColumnName;
} // the plan name
else
{
dest.Rows[r][c] = source.Rows[c - 1][r + 1];
}
}
}
dest.AcceptChanges();
return dest;
}

 

[richard-af]

public DataTable TurnTable(DataTable source)

It's times like this that:

a. I remember that the CUK Admins do real work - sometimes.

b. I remember that MS-SQL is utter crap.

 

[TheFaQQer]

public DataTable TurnTable(DataTable source)

Hang on - a useful post, that didn't ridicule the OP, and might solve a problem??

What's that doing here???