程序代码：

DECLARE @tb TABLE( 名称id VARCHAR(20), 名称 VARCHAR(20), 数量 INT, 金额 INT, 时间 VARCHAR(20))
--组
DECLARE @GroupT TABLE( 组 VARCHAR(20))

INSERT @tb( 名称id, 名称, 数量, 金额, 时间 )
VALUES
( 1002, '手机', 200, 1000, '20200701' ),
( 1002, '手机', 50, 250, '20200701' ),
( 10035, '电视', 101, 2000, '20200701' ),
( 100001, '微波炉', 165, 1700, '20200701' ),
( 1002, '手机', 100, 600, '20200702' ),
( 10035, '电视', 200, 3000, '20200702' ),
( 1002, '手机', 300, 2000, '20200703' ),
( 10035, '电视', 400, 1999, '20200703' ),
( 100001, '微波炉', 500, 3333, '20200703' ),
( 1002, '手机', 100, 600, '20200704' ),
( 100001, '微波炉', 500, 3333, '20200705' ),
( 10035, '电视', 200, 1222, '20200706' ),
( 100001, '微波炉', 300, 1666, '20200706' ),
( 10035, '电视', 100, 999, '20200706' ),
( 100009, '洗衣机', 10, 555, '20200708' ),
( 100012, '扫地机器人', 20, 888, '20200716' ),
( 100022, '笔记本电脑', 10, 3999, '20200729' ),
( 10035, '电视', 100, 999, '20200731' ),
( 100001, '微波炉', 100, 333, '20200731' ),
( 100009, '洗衣机', 10, 555, '20200731' ),
( 100012, '扫地机器人', 20, 888, '20200731' ),
( 100022, '笔记本电脑', 10, 3999, '20200731' )


--小计数量 金额
SELECT *
    INTO #tb2
    FROM( SELECT T.名称id, T.名称, SUM(T.数量) 数量, SUM(T.金额) 金额, T.时间
              FROM @tb AS T
              GROUP BY T.名称id, T.名称, T.时间 ) t

INSERT @GroupT( 组 )SELECT DISTINCT 时间 FROM #tb2
--=========================@t 拼接 加小计=======================================
DECLARE @str1 VARCHAR(4000) = 'DECLARE @t TABLE(名称id VARCHAR(20), 名称 VARCHAR(20),SUM_数量 INT ,SUM_金额 int'

SELECT @str1 = ISNULL(@str1 + ',', '') + '数量_' + GT.组 + ' INT,金额_' + GT.组 + ' INT' FROM @GroupT AS GT

SET @str1 += ') 
INSERT INTO @t '
--=========================INSERT INTO @t===============================
DECLARE @str2 VARCHAR(4000) = 'SELECT T.名称id, T.名称, 0 ,0'

SELECT @str2
    = ISNULL(@str2 + ',', '') + ' ISNULL( SUM(CASE WHEN T.时间 = ''' + GT.组 + ''' THEN T.数量 END),0) AS 数量' + '_' + GT.组
      + ',ISNULL( SUM(CASE WHEN T.时间 = ''' + GT.组 + ''' THEN T.金额 END),0) AS 金额' + '_' + GT.组
    FROM @GroupT AS GT
SET @str2 += ' FROM #tb2 AS T GROUP BY T.名称id, T.名称 ORDER BY T.名称id '
--===========================UPDATE @t=================================
DECLARE @str3 VARCHAR(4000)
DECLARE @str4 VARCHAR(4000)
DECLARE @str5 VARCHAR(4000)
DECLARE @str6 VARCHAR(4000)
SELECT @str4 = ISNULL(@str4 + 'UNION ALL ', '') + 'SELECT 数量_' + GT.组 + ' SUM_数量 ',
       @str5 = ISNULL(@str5 + 'UNION ALL ', '') + 'SELECT 金额_' + GT.组 + ' SUM_金额 ',
       @str6 = ISNULL(@str6 + ',', '') + '数量_' + GT.组 + ',金额_' + GT.组
    FROM @GroupT AS GT
SET @str3
    = 'UPDATE T SET SUM_数量 = ( SELECT SUM(SUM_数量) FROM ( ' + @str4 + ') tmp ),SUM_金额 = ( SELECT SUM(SUM_金额) FROM ( '
      + @str5 + ') tmc ) From @t T'
--===========================#tb3=================================
DECLARE @str7 VARCHAR(4000)
SELECT @str7 = ISNULL(@str7 + ',', '') + 'SUM(数量_' + GT.组 + ') 数量_' + GT.组 + ',SUM(金额_' + GT.组 + ') 金额_' + GT.组
    FROM @GroupT AS GT
SET @str7
    = ' SELECT * INTO #tb3 FROM( SELECT CASE WHEN GROUPING(名称id) = 1 AND GROUPING(名称) = 1 THEN ''合计'' ELSE 名称id END 名称id,
         CASE WHEN GROUPING(名称id) = 1 AND GROUPING(名称) = 1 THEN NULL ELSE 名称 END 名称,  SUM(SUM_数量) SUM_数量小计, SUM(SUM_金额) SUM_金额小计, '
      + @str7
      + ' FROM @t AS T
       GROUP BY ROLLUP(名称id, 名称)) tt 
             
SELECT * FROM #tb3 WHERE 名称 IS NOT NULL
UNION
SELECT * FROM #tb3 WHERE 名称id=''合计'''

--SELECT @str1 + @str2
--SELECT @str3
SELECT @str1 + @str2 + @str3 + @str7
--EXEC( @str1 + @str2 +@str3+@str7)


GO
IF OBJECT_ID('tempdb..#tb2') IS NOT NULL BEGIN
DROP TABLE #tb2
END
GO
IF OBJECT_ID('tempdb..#tb3') IS NOT NULL BEGIN
DROP TABLE #tb3
END

[此贴子已经被作者于2020-8-16 19:23编辑过]