如何使用SQL的Pivot实现目标结果？附表结构与测试数据

没问题，我来帮你搞定这个Pivot的需求！结合你的表结构和数据，我猜你想要的是按门店（storeid）汇总不同交易类型+支付方式对应的金额（或交易次数），把原本分散在行里的交易类型和支付方式转成列展示。下面一步步给你讲怎么做：

基础静态Pivot实现 #

如果你的交易类型和支付方式是固定的，直接用静态Pivot就可以，步骤如下：

1. 先准备源数据（拼接组合列） #

首先我们需要把txntype和paymode拼接成一个唯一的标识列，这样Pivot才能识别每个要转成列的项：

SELECT 
    storeid,
    CONCAT(txntype, '_', paymode) AS TransactionType_PayMode,
    amount -- 这里如果要汇总交易数就换成txncount
FROM temp

2. 套上Pivot逻辑 #

基于上面的子查询，我们用Pivot来聚合金额并转列：

SELECT 
    storeid,
    -- 处理NULL值，没有数据的话显示0
    ISNULL(Buy_Cash, 0) AS Buy_Cash,
    ISNULL(Sell_Bank, 0) AS Sell_Bank,
    ISNULL(Sell_Cash, 0) AS Sell_Cash,
    ISNULL(Sell_Cheque, 0) AS Sell_Cheque,
    ISNULL(Sell_Wallet, 0) AS Sell_Wallet
FROM (
    SELECT 
        storeid,
        CONCAT(txntype, '_', paymode) AS TransactionType_PayMode,
        amount
    FROM temp
) AS SourceTable
PIVOT (
    SUM(amount) -- 要聚合的指标，换成SUM(txncount)就是汇总交易次数
    FOR TransactionType_PayMode IN (
        [Buy_Cash], 
        [Sell_Bank], 
        [Sell_Cash], 
        [Sell_Cheque], 
        [Sell_Wallet]
    )
) AS PivotTable;

运行后你会得到预期的结果：

动态Pivot（应对可变的交易类型/支付方式） #

如果未来可能新增交易类型或支付方式，不想每次手动修改列名，可以用动态SQL自动生成所有可能的组合列：

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

-- 自动获取所有交易类型+支付方式的组合，生成列名
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(CONCAT(txntype, '_', paymode)) 
            FROM temp
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

-- 拼接动态Pivot的SQL语句
SET @query = 'SELECT storeid, ' + @cols + ' from 
             (
                SELECT 
                    storeid,
                    CONCAT(txntype, ''_'', paymode) AS TransactionType_PayMode,
                    amount
                FROM temp
            ) x
            pivot 
            (
                SUM(amount)
                FOR TransactionType_PayMode IN (' + @cols + ')
            ) p '

-- 执行动态SQL
EXECUTE(@query)

关键注意点 #

• 聚合函数：这里用SUM是因为如果同一个门店有相同交易类型+支付方式的多条记录，会自动汇总；如果只需要统计交易次数，把SUM(amount)换成SUM(txncount)或者COUNT(txncount)就行。
• NULL处理：用ISNULL可以把没有数据的列值换成0，避免显示NULL影响阅读。
• 列名格式：拼接后的列名如果有特殊字符（比如下划线），必须用方括号[]括起来，否则SQL会报错。

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