SQL Server 2008 中 PIVOT 的更新

发布于 2024-07-16 10:37:50 字数 66 浏览 6 评论 0原文

有没有办法在 SQL Server 2008 中对 PIVOTed 表执行更新，其中更改会传播回源表（假设没有聚合）？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

蓝天白云 2024-07-23 10:37:50

PIVOT 始终需要在数据透视子句中使用聚合函数。

因此总是存在聚合。

所以，不，它不能更新。

您可以根据该语句在视图上放置INSTEAD OF TRIGGER，这样您就可以使任何视图可更新。

示例此处

回复收藏 0 原文

秉烛思 2024-07-23 10:37:50

仅当旋转列形成唯一标识符时，这才真正起作用。让我们以 Buggy 为例；这是原始表：

TaskID    Date    Hours

我们希望将其转换为如下所示的表：

TaskID    11/15/1980    11/16/1980    11/17/1980 ... etc.

为了创建数据透视表，您需要执行以下操作：

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. 现在，您对其中一个时间字段执行更新：

UPDATE
    ##Pivoted
SET
    [11/16/1980 00:00:00] = 10
WHERE
    TaskID = 1234

现在 ##Pivoted 具有 1980 年 11 月 16 日发生的任务的更新版本的时间，我们希望将其保存回原始表，因此我们使用 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 字段，我如何知道要更新哪个星期日的时间？这仅在没有聚合的情况下才有效。我希望这有帮助！

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!

回复收藏 0 原文