SQL Server中行转列并按手机号统计数量的实现方法

嘿，这个需求我熟！在SQL Server里实现行转列+合并统计，其实用窗口函数配合条件聚合或者PIVOT都能搞定，我给你分几种情况来写，你可以根据自己的场景选：

方案一：条件聚合（最直观，适合固定数量的产品列） #

这种方式逻辑清晰，容易理解，适合你知道最多有几个产品的场景（比如你例子里最多2个）：

WITH RankedProducts AS (
    SELECT 
        Srno,
        Name,
        Mobile,
        ProductName,
        -- 给每个手机号下的产品按原Srno顺序编号
        ROW_NUMBER() OVER (PARTITION BY Mobile ORDER BY Srno) AS ProductRank,
        -- 直接统计每个手机号的总记录数，不用额外GROUP BY
        COUNT(*) OVER (PARTITION BY Mobile) AS TotalCount
    FROM YourTableName
)
SELECT 
    MIN(Srno) AS Srno, -- 取每个手机号组的第一条记录的Srno
    Name,
    Mobile,
    TotalCount AS Count,
    -- 把排名1的产品放到ProductName1列
    MAX(CASE WHEN ProductRank = 1 THEN ProductName END) AS ProductName1,
    -- 排名2的放到ProductName2，有更多产品就继续加
    MAX(CASE WHEN ProductRank = 2 THEN ProductName END) AS ProductName2
FROM RankedProducts
GROUP BY Name, Mobile, TotalCount
ORDER BY Srno;

为啥这么写？ #

• 先用CTE给每个手机号下的产品排个序号，这样我们知道哪个是第一个产品，哪个是第二个
• COUNT(*) OVER (PARTITION BY Mobile) 能直接算出每个手机号的总记录数，省得再做一次分组
• 外层用CASE把不同序号的产品映射到对应的列，用MAX是因为每个序号在分组里只有一个值，取最大值不影响结果

方案二：用PIVOT运算符（更“官方”的行转列写法） #

如果你更喜欢用SQL Server自带的PIVOT功能，也可以这么写：

WITH ProductCounts AS (
    SELECT 
        Mobile,
        Name,
        COUNT(*) OVER (PARTITION BY Mobile) AS TotalCount,
        ProductName,
        ROW_NUMBER() OVER (PARTITION BY Mobile ORDER BY Srno) AS ProductRank
    FROM YourTableName
)
SELECT 
    -- 重新生成目标结果里的Srno
    ROW_NUMBER() OVER (ORDER BY Mobile) AS Srno,
    Name,
    Mobile,
    TotalCount AS Count,
    -- 把PIVOT后的列名映射成ProductName1、ProductName2
    [1] AS ProductName1,
    [2] AS ProductName2
FROM ProductCounts
PIVOT (
    -- 取对应排名的产品名
    MAX(ProductName)
    -- 用ProductRank作为透视的列依据
    FOR ProductRank IN ([1], [2])
) AS PivotTable
ORDER BY Srno;

方案三：动态SQL（产品数量不固定时用） #

如果有的手机号有3个产品，有的有5个，静态写死列名就不行了，这时候用动态SQL自动生成需要的列：

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

-- 自动生成所有需要的列名，比如[1],[2],[3]...然后转成ProductName1,ProductName2...
SELECT @cols = STRING_AGG(QUOTENAME(ProductRank) + ' AS ProductName' + CAST(ProductRank AS NVARCHAR), ', ')
FROM (
    SELECT DISTINCT ROW_NUMBER() OVER (PARTITION BY Mobile ORDER BY Srno) AS ProductRank
    FROM YourTableName
) AS Ranks;

-- 拼接完整的SQL语句
SET @query = N'
WITH RankedProducts AS (
    SELECT 
        Srno,
        Name,
        Mobile,
        ProductName,
        ROW_NUMBER() OVER (PARTITION BY Mobile ORDER BY Srno) AS ProductRank,
        COUNT(*) OVER (PARTITION BY Mobile) AS TotalCount
    FROM YourTableName
)
SELECT 
    MIN(Srno) AS Srno,
    Name,
    Mobile,
    TotalCount AS Count,
    ' + @cols + N'
FROM RankedProducts
GROUP BY Name, Mobile, TotalCount
ORDER BY Srno;';

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

注意点： #

• STRING_AGG是SQL Server 2017及以上版本才支持的，如果是旧版本，得用FOR XML PATH来拼接字符串
• 测试的时候记得把代码里的YourTableName换成你实际的表名哦！

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