如何为PIVOT转置后的SELECT查询结果指定行添加$与%符号？

要高效地给Average Price行添加$符号、Share of Total Sales行添加%符号，你不需要拆分查询再拼接——只需要在PIVOT之后的最终SELECT环节，利用Name列的标识，通过CASE WHEN对不同行的数值做动态格式化即可。这种方式直接在行级处理，不会额外增加查询开销。

修改后的完整查询 #

SELECT
    [Name],
    -- 对Item1列按行类型格式化
    CASE [Name]
        WHEN 'Average Price' THEN '$' + STR([Item1], 18, 1)
        WHEN 'Share of Total Sales' THEN STR([Item1], 18, 1) + '%'
        ELSE STR([Item1], 18, 1)
    END AS [Item1],
    -- 对Item2列按行类型格式化
    CASE [Name]
        WHEN 'Average Price' THEN '$' + STR([Item2], 18, 1)
        WHEN 'Share of Total Sales' THEN STR([Item2], 18, 1) + '%'
        ELSE STR([Item2], 18, 1)
    END AS [Item2],
    -- 对Item3列按行类型格式化
    CASE [Name]
        WHEN 'Average Price' THEN '$' + STR([Item3], 18, 1)
        WHEN 'Share of Total Sales' THEN STR([Item3], 18, 1) + '%'
        ELSE STR([Item3], 18, 1)
    END AS [Item3],
    -- 对Total列按行类型格式化
    CASE [Name]
        WHEN 'Average Price' THEN '$' + STR([Total], 18, 1)
        WHEN 'Share of Total Sales' THEN STR([Total], 18, 1) + '%'
        ELSE STR([Total], 18, 1)
    END AS [Total]
FROM (
    select [Item_Type], [Name], value 
    from (
        select [Item_Type],[Total Sales],[Share of Total Sales],[Average Price],[$0 to $99],[$100 to $199],[$200+] 
        from (
            SELECT 
                [Item_Type],
                [Total Sales],
                CAST([Total Sales]*100.0/(SELECT SUM([Total Sales]) FROM My_DB.dbo.Sales_Data GROUP BY [Year],[Month]) AS DECIMAL(38,1)) [Share of Total Sales],
                [Average Price],
                [$0 to $99],
                [$100 to $199],
                [$200+] 
            FROM My_DB.dbo.Sales_Data 
            union all 
            select 
                'Total',
                SUM([Total Sales]),
                SUM([Total Sales])*100.0/(SELECT SUM([Total Sales]) FROM My_DB.dbo.Sales_Data GROUP BY [Year],[Month]) [Share of Total Sales],
                CAST(AVG([Average Price]) AS DECIMAL(38,1)),
                SUM([$0 to $99]),
                SUM([$100 to $199]),
                SUM([$200+]) 
            from My_DB.dbo.Sales_Data 
            GROUP BY [Year],[Month]
        ) d 
    ) e 
    unpivot ( 
        value for [Name] in ([Total Sales],[Share of Total Sales],[Average Price],[$0 to $99],[$100 to $199],[$200+]) 
    ) unpiv 
) src 
pivot ( 
    sum(value) for [Item_Type] in ( [Item1],[Item2],[Item3],[Total]) 
) piv;

关键细节说明 #

1. 使用STR()函数控制格式：
   用STR(column, 18, 1)替代简单的CAST，可以严格按照decimal(18,1)的要求输出1位小数，避免出现格式混乱（比如多余的小数位或科学计数法）。
2. 行级判断逻辑：
   通过CASE [Name]判断当前行的类型，只对Average Price和Share of Total Sales行做符号拼接，其他行保持原有数值格式。
3. 高效性：
   所有格式化逻辑都在PIVOT之后的结果集上处理，不会影响中间的聚合和转置逻辑，性能开销极小，完全符合你要的“行级值更新”需求。

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