时间戳

首页 > 解决方案 > 具有聚合和总计的 SQL 透视查询

sql - 具有聚合和总计的 SQL 透视查询

问题描述

表-A

产品编号 品牌
1 品牌-1
2 品牌-2
3 品牌-3

表-B

DC日期 直流无 产品编号 重量
2021-03-09 3 1 12.5
2021-03-09 3 1 12.6
2021-03-09 3 1 12.5
2021-03-09 3 2 10.5
2021-03-09 3 2 10.4
2021-03-09 3 3 15.5
2021-03-09 1 1 12.5
2021-03-09 1 3 15.7
2021-03-09 2 2 10.6
2021-03-09 4 1 12.7
2021-03-09 4 1 12.6

预期结果：-

品牌 1 2 3 4
品牌-1 1 0 3 2
品牌-2 0 1 2 0
品牌-3 1 0 1 0

需要动态透视查询

我试过的

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

select @cols = STUFF((SELECT ',' + QUOTENAME([DCNo]) from [Table-B] FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

set @query = 'SELECT ' + @cols + '
             from 
             (
                SELECT a.[DCNo] , b.[BrandName], count(*) as Total FROM [Table-B] a inner join [Table-A] b on a.[ProductID]=b.[ProductID] group by [DCNo] , b.[BrandName] order by DCNo, b.[BrandName]
            ) x
            pivot 
            (
                max(Total)
                for DCNo in (' + @cols + ')
            ) p '

execute(@query)

标签： sqldynamicpivot

解决方案

我在 sqlfiddle 中重新创建了它：http ://sqlfiddle.com/#!18/d1554/9 。你很接近，但我有时发现将所有总和/计数/分组逻辑移到查询的 PIVOT 部分更容易。产生更简单的查询（大多数时候）

CREATE TABLE [Table-A] (
  ProductId INT,
  BrandName VARCHAR(50)
)

CREATE TABLE [Table-B](
  DCDate DATETIME,
  DCNo INT,
  ProductId INT,
  Weight DECIMAL
)

INSERT INTO [Table-A] VALUES
(1, 'CAT'),
(2, 'APPLE'),
(3, 'PARROT')

INSERT INTO [Table-B] VALUES
('2021-03-09', 3,   1,  12.5),
('2021-03-09', 3,   1,  12.6),
('2021-03-09', 3,   1,  12.5),
('2021-03-09', 3,   2,  10.5),
('2021-03-09', 3,   2,  10.4),
('2021-03-09', 3,   3,  15.5),
('2021-03-09', 1,   1,  12.5),
('2021-03-09', 1,   3,  15.7),
('2021-03-09', 2,   2,  10.6),
('2021-03-09', 4,   1,  12.7),
('2021-03-09', 4,   1,  12.6)

-- Query
DECLARE @cols AS NVARCHAR(MAX) = '';
DECLARE @nullcols AS NVARCHAR(MAX) = '';
DECLARE @query  AS NVARCHAR(MAX);

-- Determine columns
;WITH cte AS (
  SELECT 
    DISTINCT 
    dcno,
    ',' + QUOTENAME([DCNo]) AS col,
    ', ISNULL(' + QUOTENAME([DCNo]) + ', 0) AS ' + QUOTENAME([DCNo]) AS nullcol
  FROM [Table-B]
)
SELECT
  @cols += col,
  @nullcols += nullcol
FROM cte
ORDER BY dcno
SET @cols = SUBSTRING(@cols, 2, LEN(@cols)) 

-- create query
SET @query = N';with CTE AS 
(
  SELECT 
    a.BrandName, 
    a.ProductId,
    b.DCNo, 
    1 as Value
  FROM [Table-A] a
  INNER JOIN [Table-B] b
     ON a.ProductId = b.ProductId
)
SELECT BrandName ' + @nullcols + ' FROM 
(SELECT * FROM cte) p
PIVOT
(
  SUM(Value)
  FOR DCNo IN (' + @cols + ')    
) AS pvt
ORDER BY pvt.ProductId
'
-- SELECT @query
execute(@query)

推荐阅读