SqlServer增加合计行以及行转列，两表连接

SELECT a.[日期],
MAX(CASE WHEN a.Years = '2019-01' THEN a.TotalStock ELSE 0 END) AS 'Jan',
MAX(CASE WHEN a.Years = '2019-02' THEN a.TotalStock ELSE 0 END) AS 'Feb',
MAX(CASE WHEN a.Years = '2019-03' THEN a.TotalStock ELSE 0 END) AS 'Mar',
MAX(CASE WHEN a.Years = '2019-04' THEN a.TotalStock ELSE 0 END) AS 'Apr',
MAX(CASE WHEN a.Years = '2019-05' THEN a.TotalStock ELSE 0 END) AS 'May',
MAX(CASE WHEN a.Years = '2019-06' THEN a.TotalStock ELSE 0 END) AS 'Jun',
MAX(CASE WHEN a.Years = '2019-07' THEN a.TotalStock ELSE 0 END) AS 'Jul',
MAX(CASE WHEN a.Years = '2019-08' THEN a.TotalStock ELSE 0 END) AS 'Aug',
MAX(CASE WHEN a.Years = '2019-09' THEN a.TotalStock ELSE 0 END) AS 'Sept',
MAX(CASE WHEN a.Years = '2019-10' THEN a.TotalStock ELSE 0 END) AS 'Oct',
MAX(CASE WHEN a.Years = '2019-11' THEN a.TotalStock ELSE 0 END) AS 'Nov',
MAX(CASE WHEN a.Years = '2019-12' THEN a.TotalStock ELSE 0 END) AS 'Dec',
MAX(CASE WHEN a.Years IS NULL THEN a.TotalStock ELSE 0 END) AS '合计'
 FROM (
SELECT  CASE WHEN GROUPING(a.[日期]) = '1' THEN '总计'
                        WHEN GROUPING(a.[日期]) = '0' AND GROUPING(a.Years) = 1 THEN a.[日期] ELSE a.[日期] END '日期',
SUM(a.TotalStock) AS TotalStock, a.Years 
FROM(
SELECT  CONVERT(varchar(7) ,b.DeliveryDate, 120) AS Years, COUNT (b.Qrcode) AS TotalStock, '退货数量' AS 日期
FROM App_AfterSalesOrder a LEFT JOIN App_AfterSalesOrderDetail b ON a.OrderNo = b.OrderNo 
LEFT JOIN Base_GoodsInfo c ON b.ProductID = replace(ltrim(replace(c.ProductID, '0', ' ')    ),    ' ',    '0')
WHERE b.DeliveryDate >= '2019-01-01' AND b.DeliveryDate < '2020-01-01' AND a.IsExam = 1 AND b.Status = 1 AND c.Standard = '6-QW-48(430)'
GROUP BY    CONVERT(varchar(7) ,b.DeliveryDate, 120)
UNION 
SELECT   CONVERT(varchar(7) ,a.AccountDate, 120) AS Years, SUM(b.ArrivalAmount) AS TotalStock, '进货数量' AS 日期
FROM Sap_DelivRecode a 
--LEFT JOIN Sap_DelivRecodeDetail b ON a.OrderNo = b.OrderNo
LEFT JOIN App_PurchaseOrderDetail b ON a.OrderNo = b.DelivNo
LEFT JOIN Base_GoodsInfo c ON b.ProductID = c.ProductID
WHERE a.AccountDate >= '2019-01-01' AND a.AccountDate < '2020-01-01' AND c.Brand IN ('超威', '象王', '莫尔')
AND c.Standard = '6-QW-48(430)'
GROUP BY    CONVERT(varchar(7) ,a.AccountDate, 120) )a
GROUP BY a.[日期], a.Years with rollup
) a
GROUP BY a.[日期]

1.增加小计总结时，with rollup 与 case when 一起使用，分组SUM求和

2.行转列 使用case when 

3.单独增加一列，直接赋值并 AS 新的字段名

4.连接表使用union，两表字段数量类型排序须一致