PRINT DATENAME(dw, GETDATE())

--建表
CREATE TABLE #temp_SalesReport (
   [Month] datetime not null,
   [Amount] money not null
)

DECLARE @MonthDiffer int --月份差
SET @MonthDiffer = 0

DECLARE @Amount money
SET @Amount = 100.00

DECLARE @StartMonth datetime --開始的月份
DECLARE @MenthCOunt int      --計算月數(shù) 范圍{ 1 ... 12 }
SET @StartMonth = CAST('2011-01-01' AS datetime)
SET @MenthCOunt = 12 --模擬一年的銷售

DECLARE @MonthDate datetime
SET @MonthDate = DATEADD(dd, -day(@StartMonth) + 1, @StartMonth)

--Debug
--SELECT @MonthDate

--插數(shù)據(jù)
WHILE (@MonthDiffer < @MenthCOunt)
BEGIN
   INSERT INTO #temp_SalesReport (
      [Month], [Amount]
   ) VALUES (
      @StartMonth, @Amount
   )
   
   SET @Amount = @Amount + 100.00
   
   SET @StartMonth = DATEADD(dd, 1, @StartMonth)
   
   SET @MonthDiffer = DATEDIFF(mm, @MonthDate, @StartMonth)
   --print @MonthDiffer
END

--Debug
--SELECT * FROM #temp_SalesReport
--DELETE FROM #temp_SalesReport

--實現(xiàn),先計算出月份和第幾周
;WITH CTE_1 AS (
   SELECT 
      (DATENAME(dd, [Month]) + 6) / 7 [WeekOfMonth], -- 加6 是 -1 + 7來的,這列是獲取當(dāng)月的周編號
      MONTH([Month]) [MonthForNum], -- 獲取月份
      --[Month], --Debug時可以取消看結(jié)果
      [Amount]
   FROM 
      [#temp_SalesReport]
) 
--select * from CTE_1 --Debug
,
CTE_2 AS (
   SELECT 
      [MonthForNum],
      [WeekOfMonth],
      [Amount]
   FROM CTE_1
)
--SELECT * FROM CTE_2 --Debug
/* Debug (最終結(jié)果)
SELECT MonthForNum, WeekOfMonth, SUM(Amount) [AmountTotal] 
FROM CTE_2 
GROUP BY MonthForNum, WeekOfMonth
ORDER BY MonthForNum, WeekOfMonth
*/
--行轉(zhuǎn)列后的最終結(jié)果
SELECT 
   CAST(MonthForNum AS NVARCHAR(2)) + N'月份' [月份],
   [1] [第一周], 
   [2] [第二周],
   [3] [第三周],
   [4] [第四周],
   CASE  
      WHEN [5] is null THEN CAST(0.00 AS money)
      ELSE [5]
   END [第五周]
FROM (
   SELECT * FROM CTE_2 
   GROUP BY MonthForNum, WeekOfMonth, Amount
) [SourceTable]
PIVOT (
   SUM([Amount])
   FOR [WeekOfMonth] IN (
      [1], [2], [3], [4], [5]
   )
) [PivotTable]