Updates on PIVOTs in SQL Server 2008

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?

From stackoverflow

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

• 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.

• 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

  brian : How could I use the INSTEAD OF trigger to update the original table?

  Cade Roux : Example here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=120679

• 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 !!

• 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!