收到错误“具有相同的键SSRS项目已被添加”在SQL Server 2014

Question

希望有人比我聪明能帮助我在这里...

我收到错误SSRS项目使用相同的密钥具有已经添加了下面的SQL我已经尝试罚款什么是代码的问题，但一直没能找到它。

有人可以帮助我了:)

SELECT 
    Name.ID, Name.COMPANY, Name.CATEGORY, 
    HOUSEPOP.HH_INV AS 'Current Year HH_INV', 
    HOUSEPOP_1.HH_INV AS 'Last Year HH_INV', 
    name.MEMBER_TYPE, name.CATEGORY, 
    HOUSEPOP.TOTAL_POP, 
    (HOUSEPOP.HH_INV - HOUSEPOP_1.HH_INV) AS 'Difference', 
    ((HOUSEPOP.HH_INV - HOUSEPOP_1.HH_INV)/HOUSEPOP_1.HH_INV) AS '% Change' 
FROM    
    Name 
INNER JOIN 
    HOUSEPOP ON Name.ID = HOUSEPOP.ID 
INNER JOIN 
    HOUSEPOP AS HOUSEPOP_1 ON Name.ID = HOUSEPOP_1.ID 
WHERE 
    HOUSEPOP.BILL_YEAR = @Bill_Year 
    AND HOUSEPOP_1.BILL_YEAR = @Bill_Year -1 
ORDER BY 
    ID 

Answer 1

你有两个字段称为“A类” - 一个“公司”后一个“MEMBER_TYPE”之后。 SSRS将只允许唯一的列名称。

Answer 2

您有2个CATEGORY列。 SQL Server/SSMS将允许查询中的重复列，但SSRS不能有重复项。你需要别名。

SELECT 
     Name.ID, 
     Name.COMPANY, 
     Name.CATEGORY,    --CATEGORY column 1 here 
     HOUSEPOP.HH_INV AS 'Current Year HH_INV', 
     HOUSEPOP_1.HH_INV AS 'Last Year HH_INV', 
     name.MEMBER_TYPE, 
     name.CATEGORY AS 'CATEGORY2', --I aliased your 2nd CATEGORY column here 
     HOUSEPOP.TOTAL_POP, 
     (HOUSEPOP.HH_INV - HOUSEPOP_1.HH_INV) as 'Difference', 
     ((HOUSEPOP.HH_INV - HOUSEPOP_1.HH_INV)/HOUSEPOP_1.HH_INV) as '% Change' 
FROM   Name 
INNER JOIN  HOUSEPOP 
ON    Name.ID = HOUSEPOP.ID 
INNER JOIN  HOUSEPOP AS HOUSEPOP_1 
ON    Name.ID = HOUSEPOP_1.ID 
WHERE   HOUSEPOP.BILL_YEAR = @Bill_Year 
AND   HOUSEPOP_1.BILL_YEAR = @Bill_Year -1 
ORDER BY  ID 

我也花了一些时间来格式化您的查询，以便它是可读的。这将帮助你调试（至少帮助我）。

编辑：现在我再次查看您的查询，CATEGORY看起来是重复的，所以答案可能只是删除第二个，而不是别名。