时间戳

首页 > 解决方案 > 在 SQL Server 中使用逐行总和进行数据透视

sql - 在 SQL Server 中使用逐行总和进行数据透视

问题描述

表-A：

PID 品牌
1 品牌1
2 品牌2
3 品牌3
4 品牌4
5 品牌5
6 品牌6
7 品牌7
8 品牌8

表-B：

客户编号 姓名 品牌 数量 安特
1 C1 品牌1 3 300
1 C1 品牌2 2 400
1 C1 品牌4 1 300
1 C1 品牌5 2 100
2 C2 品牌1 2 200
2 C2 品牌3 1 200
3 C3 品牌2 1 300
3 C3 品牌7 3 150

预期结果：-

客户编号 姓名 品牌1 品牌2 品牌3 品牌4 品牌5 品牌6 品牌7 品牌8 安特
1 C1 3 2 0 1 2 0 0 0 1100
2 C2 2 0 1 0 0 0 0 0 400
3 C3 0 1 0 0 0 0 3 0 450

我试过的枢轴： -

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

select @cols = STUFF((SELECT ',' + QUOTENAME(BrandName) from [TABLE-A] order by PID FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

set @query = 'SELECT CustNo,[Name],' + @cols + '
             from 
             (
select CustNo,[Name],Qty,SUM(cast([amt] as float)) as Amt,BrandName from [TABLE-B] group by CustNo,[Name],BrandName,Qty
            ) x
            pivot 
            (
                max(Qty)
                for brandname in (' + @cols + ')
            ) p '

execute(@query)

标签： sqlsql-servertsqlpivot

解决方案

您的查询中几乎没有错误。首先，您选择了不在您的表中的列“Slab”（可能是由于从另一个查询复制）相反，您需要选择 custno 和名称。

然后您的查询将运行，但您将为每个客户提供三行，因为每个客户在数量字段中具有三个不同的值。背后的原因是内部查询中的 group by 子句（group by CustNo,[Name],BrandName,Qty）。相反，我使用窗口函数来为每个客户求和（amt）。

我还使用了两组动态列名来消除结果中的空值。如您在代码（@cols）中使用的那样进行旋转，其他列表包含 coalesce(columnname,0) 以将 null 转换为 0。

如果您使用的是 SQL Server 2017 及更高版本，那么我建议使用 string_agg() 来连接列名，因为它在性能上更容易和更快。我在 Query#2 中使用过它。

模式和插入语句：

create table [Table-A](PID int, BrandName varchar(50));
insert into [Table-A] values(1  ,'Brand1');
insert into [Table-A] values(2  ,'Brand2');
insert into [Table-A] values(3  ,'Brand3');
insert into [Table-A] values(4  ,'Brand4');
insert into [Table-A] values(5  ,'Brand5');
insert into [Table-A] values(6  ,'Brand6');
insert into [Table-A] values(7  ,'Brand7');
insert into [Table-A] values(8  ,'Brand8');

create table [TABLE-B]( CustNo  int,Name varchar(10),BrandName varchar(50),Qty int, Amt int);
insert into [TABLE-B] values(1  ,'C1',  'Brand1',   3,  300);
insert into [TABLE-B] values(1  ,'C1',  'Brand2',   2,  400);
insert into [TABLE-B] values(1  ,'C1',  'Brand4',   1,  300);
insert into [TABLE-B] values(1  ,'C1',  'Brand5',   2,  100);
insert into [TABLE-B] values(2  ,'C2',  'Brand1',   2,  200);
insert into [TABLE-B] values(2  ,'C2',  'Brand3',   1,  200);
insert into [TABLE-B] values(3  ,'C3',  'Brand2',   1,  300);
insert into [TABLE-B] values(3  ,'C3',  'Brand7',   3,  150);

查询#1（使用 stuff() 和 xml 路径 for()）

DECLARE @cols AS NVARCHAR(MAX),
    @colsForSelect AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)
 
 
SET @colsForSelect = STUFF((SELECT  ',' + ' Coalesce('+quotename(BrandName)+',0) '+ quotename(BrandName)
            FROM [TABLE-A] order by pid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
        
select @cols = STUFF((SELECT ',' + QUOTENAME(BrandName) from [TABLE-A] order by PID FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

set @query = 'SELECT custno,name,' + @colsForSelect + ',Amt
             from 
             (
select CustNo,[Name],Qty,SUM(cast([amt] as float))over(partition by custno) as Amt,BrandName from [TABLE-B]             ) x
            pivot 
            (
                max(Qty)
                for brandname in (' + @cols + ')
            ) p '

execute(@query)

输出：

客户服务 姓名 品牌1 品牌2 品牌3 品牌4 品牌5 品牌6 品牌7 品牌8 安特
1 C1 3 2 0 1 2 0 0 0 1100
2 C2 2 0 1 0 0 0 0 0 400
3 C3 0 1 0 0 0 0 3 0 450

查询#2（使用 string_agg() 而不是 stuff() 和 xml path()）

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


select @colsForSelect= string_agg('coalesce('+BrandName+',0) '+QUOTENAME(BrandName)+' ','  ,') from [TABLE-A]

select @cols = string_agg(QUOTENAME(BrandName),',') from [TABLE-A]


set @query = 'SELECT custno,name,' + @colsForSelect + ',Amt
             from 
             (
select CustNo,[Name],Qty,SUM(cast([amt] as float))over(partition by custno) as Amt,BrandName from [TABLE-B]             ) x
            pivot 
            (
                max(Qty)
                for brandname in (' + @cols + ')
            ) p'

execute(@query)

输出：

客户服务 姓名 品牌1 品牌2 品牌3 品牌4 品牌5 品牌6 品牌7 品牌8 安特
1 C1 3 2 0 1 2 0 0 0 1100
2 C2 2 0 1 0 0 0 0 0 400
3 C3 0 1 0 0 0 0 3 0 450

db<>在这里摆弄

推荐阅读