Is there a way to perform updates on a PIVOTed table in SQL Server 2008 where the changes propagate back to the source table, assuming there is no aggregation?
Updates on PIVOTs in SQL Server 2008
This will only really work if the pivoted columns form a unique identifier. So let's take Buggy's example; here is the original table:
TaskID Date Hours
and we want to pivot it into a table that looks like this:
TaskID 11/15/1980 11/16/1980 11/17/1980 ... etc.
In order to create the pivot, you would do something like this:
DECLARE @FieldList NVARCHAR(MAX) SELECT @FieldList = CASE WHEN @FieldList <> '' THEN @FieldList + ', [' + [Date] + ']' ELSE '[' + [Date] + ']' END FROM Tasks DECLARE @PivotSQL NVARCHAR(MAX) SET @PivotSQL = ' SELECT TaskID , ' + @FieldList + ' INTO ##Pivoted FROM ( SELECT * FROM Tasks ) AS T PIVOT ( MAX(Hours) FOR T.[Date] IN (' + @FieldList + ') ) AS PVT ' EXEC(@PivotSQL)
So then you have your pivoted table in
##Pivoted. Now you perform an update to one of the hours fields:
UPDATE ##Pivoted SET [11/16/1980 00:00:00] = 10 WHERE TaskID = 1234
##Pivoted has an updated version of the hours for a task that took place on 11/16/1980 and we want to save that back to the original table, so we use an
DECLARE @UnPivotSQL NVarChar(MAX) SET @UnPivotSQL = ' SELECT TaskID , [Date] , [Hours] INTO ##UnPivoted FROM ##Pivoted UNPIVOT ( Value FOR [Date] IN (' + @FieldList + ') ) AS UP ' EXEC(@UnPivotSQL) UPDATE Tasks SET [Hours] = UP.[Hours] FROM Tasks T INNER JOIN ##UnPivoted UP ON T.TaskID = UP.TaskID
You'll notice that I modified Buggy's example to remove aggregation by day-of-week. That's because there's no going back and updating if you perform any sort of aggregation. If I update the SUNHours field, how do I know which Sunday's hours I'm updating? This will only work if there is no aggregation. I hope this helps!
I don't believe that it is possible, but if you post specifics about the actual problem that you're trying to solve someone might be able to give you some advice on a different approach to handling it.