SELECT * INTO ##GET
FROM 
  (SELECT *
    FROM ( SELECT
      CASE
      WHEN (GROUPING([檔案號]) = 1) THEN
      '合計'
      ELSE [檔案號]
      END AS '檔案號',
      CASE
      WHEN (GROUPING([系列]) = 1) THEN
      '合計'
      ELSE [系列]
      END AS '系列',
      CASE
      WHEN (GROUPING([店長]) = 1) THEN
      '合計'
      ELSE [店長]
      END AS '店長', SUM (剩余次數) AS '總剩余',
      CASE
      WHEN (GROUPING([店名]) = 1) THEN
      '合計'
      ELSE [店名]
      END AS '店名'
    FROM ##PudianCard
    GROUP BY [檔案號], [店名], [店長], [系列]
    WITH cube
    HAVING GROUPING([店名]) != 1
        AND GROUPING([檔案號]) = 1 --AND GROUPING([系列]) = 1 ) AS M
    UNION
    ALL 
      (SELECT *
        FROM ( SELECT
          CASE
          WHEN (GROUPING([檔案號]) = 1) THEN
          '合計'
          ELSE [檔案號]
          END AS '檔案號',
          CASE
          WHEN (GROUPING([系列]) = 1) THEN
          '合計'
          ELSE [系列]
          END AS '系列',
          CASE
          WHEN (GROUPING([店長]) = 1) THEN
          '合計'
          ELSE [店長]
          END AS '店長', SUM (剩余次數) AS '總剩余',
          CASE
          WHEN (GROUPING([店名]) = 1) THEN
          '合計'
          ELSE [店名]
          END AS '店名'
        FROM ##PudianCard
        GROUP BY [檔案號], [店名], [店長], [系列]
        WITH cube
        HAVING GROUPING([店名]) != 1
            AND GROUPING([店長]) != 1 ) AS P )
        UNION
        ALL 
          (SELECT *
            FROM ( SELECT
              CASE
              WHEN (GROUPING([檔案號]) = 1) THEN
              '合計'
              ELSE [檔案號]
              END AS '檔案號',
              CASE
              WHEN (GROUPING([系列]) = 1) THEN
              '合計'
              ELSE [系列]
              END AS '系列',
              CASE
              WHEN (GROUPING([店長]) = 1) THEN
              '合計'
              ELSE [店長]
              END AS '店長', SUM (剩余次數) AS '總剩余',
              CASE
              WHEN (GROUPING([店名]) = 1) THEN
              '合計'
              ELSE [店名]
              END AS '店名'
            FROM ##PudianCard
            GROUP BY [檔案號], [店名], [店長], [系列]
            WITH cube
            HAVING GROUPING([店名]) != 1
                AND GROUPING([店長]) != 1 ) AS W )
            UNION
            ALL 
              (SELECT *
                FROM ( SELECT
                  CASE
                  WHEN (GROUPING([檔案號]) = 1) THEN
                  '合計'
                  ELSE [檔案號]
                  END AS '檔案號',
                  CASE
                  WHEN (GROUPING([系列]) = 1) THEN
                  '合計'
                  ELSE [系列]
                  END AS '系列',
                  CASE
                  WHEN (GROUPING([店長]) = 1) THEN
                  '合計'
                  ELSE [店長]
                  END AS '店長', SUM (剩余次數) AS '總剩余',
                  CASE
                  WHEN (GROUPING([店名]) = 1) THEN
                  '合計'
                  ELSE [店名]
                  END AS '店名'
                FROM ##PudianCard
                GROUP BY [檔案號], [店名], [店長], [系列]
                WITH cube
                HAVING GROUPING([店名]) = 1
                    AND GROUPING([店長]) = 1
                    AND GROUPING([檔案號]) = 1 ) AS K ) ) AS T

DECLARE @st nvarchar (MAX) = '';SELECT @st =@st + 'max(case when [系列]=''' + CAST ([系列] AS VARCHAR) + ''' then [總剩余] else null end ) as [' + CAST ([系列] AS VARCHAR) + '],'
FROM ##GET
GROUP BY [系列]; print @st;

SELECT
				'select ROW_NUMBER() over(order by [卡項] desc) as [序號], [會員],[檔案號],[卡項],[剩余次數],[員工],[店名] into ' + ltrim([店名]) + ' from 查詢 where [店名]=''' + [店名] + ''' ORDER BY [卡項] desc'
		FROM
			查詢
		GROUP BY
			[店名]