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

Question

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

I am getting

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

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

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.

Answer 1

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