SQL Pivot Query with aggregation & Total

Question

表A

ProductId	BrandName
1	品牌-1
2	品牌-2
3	品牌-3

我在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)