SQL Server存储过程返回多余行：如何按指定ObjectName获取透视结果

问题场景 #

现有两张表：

ItemTable #

PropertyTable #

编写存储过程根据传入的ObjectName返回透视结果，但传入'Object01'时，结果中包含了Object02和Object03的行，不符合预期。

问题原因 #

1. 生成透视列名时虽过滤了指定ObjectName的条目，但实际查询的数据源（src子查询）未过滤数据，导致所有对象的记录都被纳入透视，其他对象的对应列值为NULL，但行仍然保留。
2. 代码存在表名错误：PropertyItem应为PropertyTable。
3. 尝试添加WHERE子句时写法有误，未覆盖UNION的两个分支。

解决方案 #

有两种可靠的过滤方式，可任选其一或结合使用：

• 方式1：在UNION的两个查询分支中分别添加WHERE条件，只保留目标ObjectName的记录。
• 方式2：在透视后的最终结果中过滤ObjectName列，写法更简洁。

同时，存储过程需使用参数接收传入的ObjectName，避免硬编码，并保持大小写不敏感匹配。

修正后的存储过程代码 #

CREATE PROCEDURE GetPivotedObjectData
    @ObjectName NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @cols AS NVARCHAR(MAX) = '';    
    DECLARE @cols1 AS NVARCHAR(MAX) = '';   
    DECLARE @query AS NVARCHAR(MAX) = '';
    DECLARE @lowerObjectName NVARCHAR(100) = LOWER(@ObjectName);

    -- 生成Item相关的透视列
    SELECT @cols = STRING_AGG(QUOTENAME(ItemName), ',')
    FROM (
        SELECT DISTINCT ItemName 
        FROM ItemTable 
        WHERE LOWER(ObjectName) = @lowerObjectName
    ) AS tmp;

    -- 生成Property相关的透视列
    SELECT @cols1 = STRING_AGG(QUOTENAME(PropertyName), ',')
    FROM (
        SELECT DISTINCT PropertyName 
        FROM PropertyTable 
        WHERE LOWER(ObjectName) = @lowerObjectName
    ) AS tmp1;

    -- 拼接透视查询语句，在最终结果中过滤ObjectName
    SET @query = '
    SELECT * FROM 
    (    
        SELECT  
            I.ItemName AS [Name]        
            ,CAST(I.ItemIsEnabled AS VARCHAR(50)) AS [ValueColumn]
            ,I.ObjectName AS [ObjectName]
        FROM ItemTable I

        UNION ALL
        SELECT 
            P.PropertyName as [Name]
            ,CAST(P.PropertyIsEnabled AS VARCHAR(50)) AS [ValueColumn]
            ,P.ObjectName AS [ObjectName]
        FROM PropertyTable P
    ) src
    PIVOT
    (
        MAX(ValueColumn) FOR [Name] IN (' + ISNULL(@cols, '') + CASE WHEN @cols IS NOT NULL AND @cols1 IS NOT NULL THEN ',' ELSE '' END + ISNULL(@cols1, '') + ')
    ) piv
    WHERE LOWER(ObjectName) = ''' + @lowerObjectName + '''';

    -- 执行动态SQL
    EXEC sp_executesql @query;
END

代码说明 #

• 使用STRING_AGG替代循环拼接列名（SQL Server 2017+支持，低版本可保留原拼接方式）。
• 修正了错误表名PropertyItem为PropertyTable。
• 移除不必要的CROSS APPLY，直接在SELECT中转换列值。
• 用UNION ALL替代UNION（无需去重，性能更优）。
• 在透视结果中添加WHERE条件，确保仅返回目标对象的行。
• 使用存储过程参数@ObjectName，灵活接收输入值。

内容的提问来源于stack exchange，提问作者SoftwareDveloper