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?
相关问题
- SQL join to get the cartesian product of 2 columns
- sql execution latency when assign to a variable
- Difference between Types.INTEGER and Types.NULL in
- What is the best way to cache a table from a (SQL)
- php PDO::FETCH_ASSOC doesnt detect select after ba
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.
PIVOT
s 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
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:
and we want to pivot it into a table that looks like this:
In order to create the pivot, you would do something like this:
So then you have your pivoted table in
##Pivoted
. Now you perform an update to one of the hours fields: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 anUNPIVOT
: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!