Updating an UNPIVOTted SQL Table

Question

Okay, I've been beating my head against this, and I'm sure I'm just missing something obvious, but...

I have a table in a customer's database, that's basically:

Item_Set_Key int
Item_1       bit
Notes_1      nvarchar(80)
Item_2       bit
Notes_2      nvarchar(80)
Item_3       bit
Notes_3      nvarchar(80)
...

There's 99 items in each record, and no changing the schema is not an option (other external considerations involved).

However, in order to display it in anything remotely resembling intelligence to the user, we have to UNPIVOT it (via a View) like this:

SELECT i.Item_Set_Key, i.Item_Number, i.Selected, i.Item, i2.Notes, i2.Note
FROM (
SELECT Item_Set_Key, SUBSTRING (Item, 6, 2) AS Item_Number, Selected, Item
    FROM Item_Set
    UNPIVOT (Selected FOR Item IN
        (Item_1, Item_2, Item_3, Item_4, Item_5, ...)
    ) as u
) AS i
LEFT JOIN (
SELECT Item_Set_Key, SUBSTRING (Note, 7, 2) AS Item_Number, Notes
    FROM Item_Set
    UNPIVOT (Notes FOR Note IN
     (Notes_1, Notes_2, Notes_3, Notes_4, Notes_5, ...)
    ) as n
) AS i2 ON i2.Item_Set_Key = i.Item_Set_Key
    AND i2.Item_Number = i.Item_Number

I do the standard bind of that to the grid. However, I'm at something of a loss as to how to build the UpdateCommand for it, because the text would have to explicitly name the column in the SET, but the column names are dynamic in the Item and Note columns, and I can't just set all of the columns because each record would only have data for one item/note pair.

Ideas?

Answer 1

You won't be able to rely on data-binding to send the changes back to the database in the original pivoted table. Instead you need to capture each update as a single "unit of work." For example, item id=92, value="Tom". Perhaps previously item 92's value was "Joe." Your unit of work here is to change the value for item 92.

As the user makes changes via the user interface you can batch together each UOW and hold on to them until they're ready to click save. When the user asks for the save, each captured UOW is "played" against the database. Search around for the "command" pattern and/or some of Jeremy Miller's articles.

Another thought is you said you can't change the schema but maybe you really can. Consider creating a real table that is in the unpivoted format. Then replace the current table with a view that uses the PIVOT command. In effect you store the data in a better design but for the existing application you PIVOT it back. This could work unless you need to do updates while it's in the pivoted design.

Last option is you could simply maintain two physical tables and then write some complex merge operation to synchronize them periodically.