SQL Server 2008中PIVOT的更新 [英] Updates on PIVOTs in SQL Server 2008

问题描述

是否有一种方法可以在SQL Server 2008中的PIVOTed表上执行更新，假定没有聚合，更改会传播回源表?

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?

推荐答案

这仅在透视列形成唯一标识符的情况下才有效.因此，让我们以Buggy为例.这是原始表:

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)

因此，您将数据透视表保存在##Pivoted中.现在，您对小时字段之一进行更新:

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具有在1980年11月16日执行的任务的小时更新版本，我们希望将其保存回原始表，因此我们使用UNPIVOT:

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

您会注意到，我修改了Buggy的示例以按星期删除聚合.这是因为如果执行任何类型的聚合，都不会返回和更新.如果我更新SUNHours字段，我怎么知道我要更新哪个星期天的时间?这仅在没有聚合的情况下有效.我希望这会有所帮助！

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!

这篇关于SQL Server 2008中PIVOT的更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！