SQL语句数据透视求和

[猕猴晓桃]

SQL语句怎么写
原始数据如下



生成以下两种数据透视的效果

[lhz_dxm]

描述的不清不楚

[RINK_1]

CREATE TABLE #T
(DT VARCHAR(10) ,
 A VARCHAR(10),
 B INT,
 C INT,
 D INT)

INSERT INTO #T
SELECT '2020070600','A',10,20,30 UNION ALL
SELECT '2020070600','B',100,200,300 UNION ALL
SELECT '2020070600','C',1000,2000,3000 UNION ALL
SELECT '2020070600','B',20,300,500 UNION ALL
SELECT '2020070606','A',101,201,301 UNION ALL
SELECT '2020070606','AA',102,202,302 UNION ALL
SELECT '2020070606','B',103,203,303 UNION ALL
SELECT '2020070606','A',100,200,300 

SELECT * FROM #T ORDER BY DT

SELECT CASE WHEN GROUPING(A)=1 AND GROUPING(DT)=0 THEN DT 
WHEN GROUPING(A)=0 AND GROUPING(DT)=0 THEN A 
ELSE '合计' END AS LABEL,
SUM(B) AS TOTAL_B,
SUM(C) AS TOTAL_C,
SUM(D) AS TOTAL_D
FROM #T
GROUP BY DT,A WITH CUBE
HAVING NOT (GROUPING(DT)=1 AND GROUPING(A)=0)
ORDER BY GROUPING(DT),DT,GROUPING(A) DESC,A


SELECT CASE WHEN GROUPING(A)=1 THEN '合计' ELSE A END AS LABEL,
SUM(B) AS B,
SUM(C) AS C,
SUM(D) AS D
FROM #T
GROUP BY A WITH CUBE