Updates on PIVOTs in SQL Server 2008

By : brian
Source: Stackoverflow.com
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?

By : brian


Answers

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!

By : brian


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

By : Cade Roux


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.

By : Tom H


This video can help you solving your question :)
By: admin