SQL 数据透视表是只读的，单元格不能编辑? [英] SQL pivoted table is read-only and cells can't be edited?

问题描述

如果我使用这个数据透视表查询创建一个 VIEW，它是不可编辑的.单元格是只读的，并给我 SQL2005 错误:没有更新行.第 2 行中的数据未提交.更新或插入视图或函数 'VIEWNAME' 失败，因为它包含派生字段或常量字段."

If I create a VIEW using this pivot table query, it isn't editable. The cells are read-only and give me the SQL2005 error: "No row was updated. The data in row 2 was not committed. Update or insert of view or function 'VIEWNAME' failed because it contains a derived or constant field."

关于如何解决这个问题的任何想法，或者像这样的支点永远无法编辑?

Any ideas on how this could be solved OR is a pivot like this just never editable?

SELECT     n_id,
MAX(CASE field WHEN 'fId' THEN c_metadata_value ELSE ' ' END) AS fId,
MAX(CASE field WHEN 'sID' THEN c_metadata_value ELSE ' ' END) AS sID,
MAX(CASE field WHEN 'NUMBER' THEN c_metadata_value ELSE ' ' END) AS NUMBER
FROM   metadata
GROUP BY n_id

推荐答案

您必须在视图上创建触发器，因为无法直接更新:

You'll have to create trigger on view, because direct update is not possible:

CREATE TRIGGER TrMyViewUpdate on MyView
INSTEAD OF UPDATE
AS
BEGIN
   SET NOCOUNT ON;
   UPDATE MyTable
   SET ...
   FROM INSERTED...
END

这篇关于SQL 数据透视表是只读的，单元格不能编辑?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！