为“数据透视表"多次指定了"ID"列 [英] The column 'ID' was specified multiple times for 'PivotTable'

问题描述

SELECT PartyRelationshipID,[2] AS OrderGroup,[3] AS TaxStatus,[4] AS Area
FROM (
SELECT prs.PartyRelationshipID
,prs.PartyRelationshipSettingTypeID
,Value = CONVERT(INT, CASE WHEN IsNumeric(CONVERT(VARCHAR(12), prs.Value)) = 1 then CONVERT(VARCHAR(12), prs.Value) else 0 End)  
FROM [Party].PartyRelationshipSetting prs) AS SourceTable
INNER JOIN Party.PartyRelationship prship ON SourceTable.PartyRelationshipID = prship.ID
INNER JOIN Party.PartyRole pr ON prship.ToPartyRoleID = pr.ID
INNER JOIN Party.Organization org ON pr.PartyID = org.PartyID
PIVOT
(
SUM(Value)
FOR PartyRelationshipSettingTypeID IN ([2],[3],[4])
)AS PivotTable

我得到了

为数据透视表"多次指定了"ID"列

The column 'ID' was specified multiple times for 'PivotTable'

但是在上面的查询中，我没有使用SELECT *语句，或者我为每个ID选择添加了别名以避免冲突.该错误从哪部分发生以及如何解决?

But in the above query I not using SELECT * statements or I am adding aliases for each and every ID selection to avoid conflicts. From which part this error occurs and how to fix it?

将以下行添加到代码中时出现此错误.

I am getting this error when I add the below lines to the code.

INNER JOIN Party.PartyRole pr ON prship.ToPartyRoleID = pr.ID
INNER JOIN Party.Organization org ON pr.PartyID = org.PartyID

以及在何处启动WHERE子句，以便仅基于prship.ToPartyRoleID选择条目. WHERE子句不适用于PIVOT的内部或外部.

And also where to pul the WHERE clause in order to only select entries based on prship.ToPartyRoleID. WHERE clause doesn't fit inside the or outside the PIVOT.

推荐答案

由于多个表具有ID列，因此您不能使用该语法进行数据透视，请在subselect中选择所需的列并进行数据透视

Since more than one table has ID column you cannot use that syntax for pivot, select the required columns in subselect and do the pivot

SELECT PartyRelationshipID,
       [2] AS OrderGroup,
       [3] AS TaxStatus,
       [4] AS Area
FROM   (SELECT PartyRelationshipID, Value, PartyRelationshipSettingTypeID --its important to select only the required columns
        FROM   (SELECT prs.PartyRelationshipID,
                       prs.PartyRelationshipSettingTypeID,
                       Value = CONVERT(INT, CASE
                                              WHEN Isnumeric(CONVERT(VARCHAR(12), prs.Value)) = 1 THEN CONVERT(VARCHAR(12), prs.Value)
                                              ELSE 0
                                            END)
                FROM   [Party].PartyRelationshipSetting prs) AS SourceTable
               INNER JOIN Party.PartyRelationship prship
                       ON SourceTable.PartyRelationshipID = prship.ID
               INNER JOIN Party.PartyRole pr
                       ON prship.ToPartyRoleID = pr.ID
               INNER JOIN Party.Organization org
                       ON pr.PartyID = org.PartyID)a 
PIVOT(SUM(Value) FOR PartyRelationshipSettingTypeID IN ([2],[3],[4]))AS PivotTable

这篇关于为“数据透视表"多次指定了"ID"列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！