Pivot DataTable with Unknow number of columns

Question

How would you go about pivoting data in a datatable where the number of columns varies from? A few columns would always be in the datatable such as ID and Name but the rest could vary. I have been struggling a lot with this and can't seem to come up with an elegant solution. Does anyone have a suggestion?

Answer 1

Use a "Property" and "PropertyVal" table to define these "virtual columns"

The property table would store meta information about the property such as caption, datatype, nullable, default value, etc.

The PropertyVal table would have

EntityID
PropertyID
Value

When you pivot you combine the known columns such as ID and Name from the entity table and then use the meta data in the property table to create these virtual columns and use the data in the PropertyVal table to spray the virtual property values in.