Updates on PIVOTs in SQL Server 2008

Question

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?

Answer 1

this is just a guess, but can you make the query into a view and then update it?

Answer 2

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.

Answer 3

PIVOTs always require an aggregate function in the pivot clause.

Thus there is always aggregation.

So, no, it cannot be updatable.

You CAN put an INSTEAD OF TRIGGER on a view based on the statement and thus you can make any view updatable.

Example here

Answer 4

I have similar problem. There is a timesheet table like this:

TaskID Date Hours

Since I want always to have this data filtered for one week, I would like to have table pivoted about date in the week:

TaskID MONHours TUEHours WEDHours ... SUNHours

This pivoted table should be presented to end user, where he could add,delete and modify hours, and also add new Tasks into that pivoted table.

After he submit that data, data should be processed to original table.

Any ideas will be really appreciated !!

Answer 5

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

Now ##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 UNPIVOT:

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!