SET LANGUAGE 繁體中文;

WITH DateRange (D) AS
(
select D = CAST ('20200101' as DATE)
union all
select CAST (DATEADD(DAY, 1, D) as DATE)
from DateRange
where D < CAST ('20201231' as DATE)
) , DR as (
SELECT DATEPART( week , D ) WeekNum , D , DATENAME(dw , D) W
FROM DateRange
)
SELECT WeekNum 週 , 星期日 , 星期一, 星期二, 星期三, 星期四, 星期五, 星期六
FROM DR
PIVOT (
MAX(D)
FOR W IN (星期日 , 星期一, 星期二, 星期三, 星期四, 星期五, 星期六)
) p
--注意這行是讓遞迴無限
OPTION (MAXRECURSION 0)

修整一下，不用TAB CHAR，SSMS複制不到TAB

DECLARE @Y AS INT 
SET @Y = 2020
DECLARE @firstday as DATETIME
DECLARE @lastday as DATETIME
DECLARE @W AS INT 
DECLARE @M AS INT 
SET @M=1
DECLARE @D AS INT 
DECLARE @WSTR AS VARCHAR(100)
SET @WSTR = '星期日  星期一  星期二  星期三  星期四  星期五  星期六'
DECLARE @F AS INT 
DECLARE @OUTPUT AS VARCHAR(MAX)
DECLARE @LN AS INT 
DECLARE @ND AS VARCHAR(2)

WHILE @M<=12    -- 一月跑到十二月
BEGIN	
	PRINT CONCAT(@M,'月')
	PRINT @WSTR  -- 輸出星期天到星期六
	SET @firstday= DATETIMEFROMPARTS(@Y,@M,1,0,0,0,0)  -- 取得該月第一天
	SET @lastday = DATEADD(MONTH,1, @firstday) -- 最後一天
	SET @W = DATEPART(WEEKDAY,@firstday) -- 第一天星期幾
	SET @OUTPUT = SPACE((@W-1)*8) -- 第一天的左邊墊空白
	SET @D = 1  -- 從該月一號開始跑到最後一天，用DATEDIFF算整月有幾天
	WHILE @D <= DATEDIFF(day, @firstday,@lastday)
	BEGIN
		SET @ND=RIGHT(@D+100,2)  -- SQL沒有PADLEFT函數可用，這招最賊，取2位墊0
		SET @LN=LEN(@ND)   -- (廢言，@ND一定是2，因為上一行)
		SET @OUTPUT = CONCAT(@OUTPUT,@ND,SPACE(8-@LN)) -- 輸出跟日期跟右墊空
            -- 我也不知道為什麼(第一天@W+迴圈@D)除7餘1會是一周結束
            IF (@D+@W)%7 = 1 
                SET @OUTPUT = CONCAT(@OUTPUT,CHAR(13))  -- 周結束，加斷行
		SET @D = @D + 1  		
	END
	SET @OUTPUT = CONCAT(@OUTPUT,CHAR(13))	-- 月份結束，一樣斷行
	PRINT @OUTPUT  -- 全月輸出
	SET @M = @M + 1  -- 下個月
END

 
1月
星期日  星期一  星期二  星期三  星期四  星期五  星期六
                        01      02      03      04      
05      06      07      08      09      10      11      
12      13      14      15      16      17      18      
19      20      21      22      23      24      25      
26      27      28      29      30      31      

2月
星期日  星期一  星期二  星期三  星期四  星期五  星期六
                                                01      
02      03      04      05      06      07      08      
09      10      11      12      13      14      15      
16      17      18      19      20      21      22      
23      24      25      26      27      28      29      


3月
星期日  星期一  星期二  星期三  星期四  星期五  星期六
01      02      03      04      05      06      07      
08      09      10      11      12      13      14      
15      16      17      18      19      20      21      
22      23      24      25      26      27      28      
29      30      31      

4月
星期日  星期一  星期二  星期三  星期四  星期五  星期六
                        01      02      03      04      
05      06      07      08      09      10      11      
12      13      14      15      16      17      18      
19      20      21      22      23      24      25      
26      27      28      29      30      

5月
星期日  星期一  星期二  星期三  星期四  星期五  星期六
                                        01      02      
03      04      05      06      07      08      09      
10      11      12      13      14      15      16      
17      18      19      20      21      22      23      
24      25      26      27      28      29      30      
31      

6月
星期日  星期一  星期二  星期三  星期四  星期五  星期六
        01      02      03      04      05      06      
07      08      09      10      11      12      13      
14      15      16      17      18      19      20      
21      22      23      24      25      26      27      
28      29      30      

7月
星期日  星期一  星期二  星期三  星期四  星期五  星期六
                        01      02      03      04      
05      06      07      08      09      10      11      
12      13      14      15      16      17      18      
19      20      21      22      23      24      25      
26      27      28      29      30      31      

8月
星期日  星期一  星期二  星期三  星期四  星期五  星期六
                                                01      
02      03      04      05      06      07      08      
09      10      11      12      13      14      15      
16      17      18      19      20      21      22      
23      24      25      26      27      28      29      
30      31      

9月
星期日  星期一  星期二  星期三  星期四  星期五  星期六
                01      02      03      04      05      
06      07      08      09      10      11      12      
13      14      15      16      17      18      19      
20      21      22      23      24      25      26      
27      28      29      30      

10月
星期日  星期一  星期二  星期三  星期四  星期五  星期六
                                01      02      03      
04      05      06      07      08      09      10      
11      12      13      14      15      16      17      
18      19      20      21      22      23      24      
25      26      27      28      29      30      31      


11月
星期日  星期一  星期二  星期三  星期四  星期五  星期六
01      02      03      04      05      06      07      
08      09      10      11      12      13      14      
15      16      17      18      19      20      21      
22      23      24      25      26      27      28      
29      30      

12月
星期日  星期一  星期二  星期三  星期四  星期五  星期六
                01      02      03      04      05      
06      07      08      09      10      11      12      
13      14      15      16      17      18      19      
20      21      22      23      24      25      26      
27      28      29      30      31      

MySQL方式,我只有區分月份,沒加上分割線.

with recursive curr_year(n, dt) as (
select 1
     , date_format(sysdate(), '%Y-01-01')
union all
select n + 1
     , date_add(date_format(sysdate(), '%Y-01-01'), INTERVAL n DAY)
  from curr_year
 where dt < date_format(sysdate(), '%Y-12-31')
),
data as (
select dt
     , dayofweek(dt) dow
     , sum(case when dayofweek(dt) = 1 or dayofmonth(dt) = 1 then 1
           else 0
           end) over(order by dt) week_no
  from curr_year
)
select date_format(min(dt), '%b') as 'month'
     , max(case when dow = 1 then date_format(dt, '%d') end) sun
     , max(case when dow = 2 then date_format(dt, '%d') end) mon
     , max(case when dow = 3 then date_format(dt, '%d') end) tue
     , max(case when dow = 4 then date_format(dt, '%d') end) wed
     , max(case when dow = 5 then date_format(dt, '%d') end) thu
     , max(case when dow = 6 then date_format(dt, '%d') end) fri
     , max(case when dow = 7 then date_format(dt, '%d') end) sat
  from data
 group by week_no
 order by week_no;
 
+-------+------+------+------+------+------+------+------+
| month | sun  | mon  | tue  | wed  | thu  | fri  | sat  |
+-------+------+------+------+------+------+------+------+
| Jan   | NULL | NULL | NULL | 01   | 02   | 03   | 04   |
| Jan   | 05   | 06   | 07   | 08   | 09   | 10   | 11   |
| Jan   | 12   | 13   | 14   | 15   | 16   | 17   | 18   |
| Jan   | 19   | 20   | 21   | 22   | 23   | 24   | 25   |
| Jan   | 26   | 27   | 28   | 29   | 30   | 31   | NULL |
| Feb   | NULL | NULL | NULL | NULL | NULL | NULL | 01   |
| Feb   | 02   | 03   | 04   | 05   | 06   | 07   | 08   |
| Feb   | 09   | 10   | 11   | 12   | 13   | 14   | 15   |
| Feb   | 16   | 17   | 18   | 19   | 20   | 21   | 22   |
| Feb   | 23   | 24   | 25   | 26   | 27   | 28   | 29   |
| Mar   | 01   | 02   | 03   | 04   | 05   | 06   | 07   |
| Mar   | 08   | 09   | 10   | 11   | 12   | 13   | 14   |
| Mar   | 15   | 16   | 17   | 18   | 19   | 20   | 21   |
| Mar   | 22   | 23   | 24   | 25   | 26   | 27   | 28   |
| Mar   | 29   | 30   | 31   | NULL | NULL | NULL | NULL |
| Apr   | NULL | NULL | NULL | 01   | 02   | 03   | 04   |
| Apr   | 05   | 06   | 07   | 08   | 09   | 10   | 11   |
| Apr   | 12   | 13   | 14   | 15   | 16   | 17   | 18   |
| Apr   | 19   | 20   | 21   | 22   | 23   | 24   | 25   |
| Apr   | 26   | 27   | 28   | 29   | 30   | NULL | NULL |
| May   | NULL | NULL | NULL | NULL | NULL | 01   | 02   |
| May   | 03   | 04   | 05   | 06   | 07   | 08   | 09   |
| May   | 10   | 11   | 12   | 13   | 14   | 15   | 16   |
| May   | 17   | 18   | 19   | 20   | 21   | 22   | 23   |
| May   | 24   | 25   | 26   | 27   | 28   | 29   | 30   |
| May   | 31   | NULL | NULL | NULL | NULL | NULL | NULL |
| Jun   | NULL | 01   | 02   | 03   | 04   | 05   | 06   |
| Jun   | 07   | 08   | 09   | 10   | 11   | 12   | 13   |
| Jun   | 14   | 15   | 16   | 17   | 18   | 19   | 20   |
| Jun   | 21   | 22   | 23   | 24   | 25   | 26   | 27   |
| Jun   | 28   | 29   | 30   | NULL | NULL | NULL | NULL |
| Jul   | NULL | NULL | NULL | 01   | 02   | 03   | 04   |
| Jul   | 05   | 06   | 07   | 08   | 09   | 10   | 11   |
| Jul   | 12   | 13   | 14   | 15   | 16   | 17   | 18   |
| Jul   | 19   | 20   | 21   | 22   | 23   | 24   | 25   |
| Jul   | 26   | 27   | 28   | 29   | 30   | 31   | NULL |
| Aug   | NULL | NULL | NULL | NULL | NULL | NULL | 01   |
| Aug   | 02   | 03   | 04   | 05   | 06   | 07   | 08   |
| Aug   | 09   | 10   | 11   | 12   | 13   | 14   | 15   |
| Aug   | 16   | 17   | 18   | 19   | 20   | 21   | 22   |
| Aug   | 23   | 24   | 25   | 26   | 27   | 28   | 29   |
| Aug   | 30   | 31   | NULL | NULL | NULL | NULL | NULL |
| Sep   | NULL | NULL | 01   | 02   | 03   | 04   | 05   |
| Sep   | 06   | 07   | 08   | 09   | 10   | 11   | 12   |
| Sep   | 13   | 14   | 15   | 16   | 17   | 18   | 19   |
| Sep   | 20   | 21   | 22   | 23   | 24   | 25   | 26   |
| Sep   | 27   | 28   | 29   | 30   | NULL | NULL | NULL |
| Oct   | NULL | NULL | NULL | NULL | 01   | 02   | 03   |
| Oct   | 04   | 05   | 06   | 07   | 08   | 09   | 10   |
| Oct   | 11   | 12   | 13   | 14   | 15   | 16   | 17   |
| Oct   | 18   | 19   | 20   | 21   | 22   | 23   | 24   |
| Oct   | 25   | 26   | 27   | 28   | 29   | 30   | 31   |
| Nov   | 01   | 02   | 03   | 04   | 05   | 06   | 07   |
| Nov   | 08   | 09   | 10   | 11   | 12   | 13   | 14   |
| Nov   | 15   | 16   | 17   | 18   | 19   | 20   | 21   |
| Nov   | 22   | 23   | 24   | 25   | 26   | 27   | 28   |
| Nov   | 29   | 30   | NULL | NULL | NULL | NULL | NULL |
| Dec   | NULL | NULL | 01   | 02   | 03   | 04   | 05   |
| Dec   | 06   | 07   | 08   | 09   | 10   | 11   | 12   |
| Dec   | 13   | 14   | 15   | 16   | 17   | 18   | 19   |
| Dec   | 20   | 21   | 22   | 23   | 24   | 25   | 26   |
| Dec   | 27   | 28   | 29   | 30   | 31   | NULL | NULL |
+-------+------+------+------+------+------+------+------+
62 rows in set (0.04 sec)

CREATE or replace FUNCTION getLastSunday(theDate date) RETURNS date AS $$
	declare
		theWeekday integer := extract(DOW from theDate);		
	BEGIN
			RETURN theDate - theWeekday * INTERVAL '1 day' ;
	END;
$$ LANGUAGE plpgsql;


CREATE or replace FUNCTION getNextSaturday(theDate date) RETURNS date AS $$
	declare
		theWeekday integer := extract(DOW from theDate);		
	BEGIN
			RETURN theDate + (6 - theWeekday)  * INTERVAL '1 day' ;
	END;
$$ LANGUAGE plpgsql;

CREATE or replace FUNCTION genWeekDays(bOW date) returns text[]  AS $$
	declare
		weekDays text[] := ARRAY[]::text[];
		counter integer := 0;
	BEGIN
		loop
			exit when counter = 7 ;
			weekDays = array_append(weekDays, date_part('day', bOW+ counter * INTERVAL '1 day')::text);
			counter := counter + 1 ; 
		end loop;
		return weekDays;
	END;
$$ LANGUAGE plpgsql;

CREATE or replace FUNCTION genMonArrays(year integer, month integer) returns text[][] AS $$
	declare
		bOM date := make_date(year, month, 1);
		eOM date;
		bOC date;
		eOC date;
		res text[][] := Array[[year::text, '年', month::text, '月', '', '', ''],['星期天','星期一','星期二','星期三','星期四','星期五','星期六']];
	BEGIN
		if month = 12 then
			eOM = make_date(year+1, 1, 1) - INTERVAL '1 day';
		else
			eOM = make_date(year, month+1, 1) - INTERVAL '1 day';
		end if;
		bOC = getLastSunday(bOM);
		eOC = getNextSaturday(eOM);
		
		declare
			tmp text[];
			i integer := 0;
		begin
			loop
				exit when i >= DATE_PART('day', eOC::timestamp - bOC::timestamp)::integer;
				tmp := genWeekDays((bOC + i * INTERVAL '1 day')::date);
				res = res || tmp;
				i := i + 7;
			end loop;
		end;
		res = res || Array['', '', '', '', '', '', ''];
		return res;
	END;
$$ LANGUAGE plpgsql;

CREATE or replace FUNCTION genYearTable(year integer) returns table(
	"SUN" text,
	"MON" text,
	"TUE" text,
	"WED" text,
	"THU" text,
	"FRI" text,
	"SAT" text
) AS $$
	declare
		month integer := 1;
		weeks text[][];
	BEGIN
		drop tABLE IF EXISTS calendar;
		CREATE TABLE calendar(
			"SUN" text,
			"MON" text,
			"TUE" text,
			"WED" text,
			"THU" text,
			"FRI" text,
			"SAT" text
			);
		loop
			exit when month > 12;
			weeks := genMonArrays(year, month);
			declare
				w text[];
			begin
				foreach w slice 1 in Array weeks loop
					insert into calendar values(w[1], w[2], w[3], w[4], w[5], w[6], w[7]);
				end loop;
			end;
			month := month + 1;
		end loop;
	END;
$$ LANGUAGE plpgsql;

select genYearTable(2020);
select * from calendar;

plpgsql版本...
基本思維和我的python版本一樣。

使用 MSSQL，寫法和 weber87na 差不多

想不到怎麼加分隔線，用 UNION ALL 的話就不漂亮了

DECLARE @SDATE DATE
DECLARE @EDATE DATE
SET @SDATE = '2020-01-01'
SET @EDATE = '2020-12-31'

-- 產生該月份所有的日期
;WITH DATE_CTE AS
(
	SELECT @SDATE AS D
	UNION ALL
	SELECT DATEADD(DAY, 1, D) 
	FROM DATE_CTE 
	WHERE D <= @EDATE
)
-- 對日期加上月份、週次、星期
, INFO_CTE AS
(
	SELECT DATEPART(MONTH, D) AS M,
	       DATEPART(WEEK, D) AS W,
		   DATEPART(WEEKDAY, D) AS WD,
		   DATEPART(DAY, D) AS D
	FROM DATE_CTE 
)
-- 將資料轉成日曆格式
, PIVOT_CTE AS
(
	SELECT [M] AS '月份',
	       [1] AS 星期日,
	       [2] AS 星期一,
		   [3] AS 星期二,
		   [4] AS 星期三,
		   [5] AS 星期四,
		   [6] AS 星期五,
		   [7] AS 星期六
	FROM INFO_CTE AS T
	PIVOT (
		MAX(D)
		FOR WD IN ([1], [2], [3], [4], [5], [6], [7])
	) AS PT
)

SELECT * FROM PIVOT_CTE
OPTION (MAXRECURSION 0)

SQL

結果

+------+--------+--------+--------+--------+--------+--------+--------+
| 月份 |  星期日 | 星期一 | 星期二  | 星期三 | 星期四  | 星期五 | 星期六 |
+------+--------+--------+--------+--------+--------+--------+--------+
| 1    | NULL   | NULL   | NULL   | 1      | 2      | 3      | 4      |
| 1    | 5      | 6      | 7      | 8      | 9      | 10     | 11     |
| 1    | 12     | 13     | 14     | 15     | 16     | 17     | 18     |
| 1    | 19     | 20     | 21     | 22     | 23     | 24     | 25     |
| 1    | 26     | 27     | 28     | 29     | 30     | 31     | NULL   |
| 2    | NULL   | NULL   | NULL   | NULL   | NULL   | NULL   | 1      |
| 2    | 2      | 3      | 4      | 5      | 6      | 7      | 8      |
| 2    | 9      | 10     | 11     | 12     | 13     | 14     | 15     |
| 2    | 16     | 17     | 18     | 19     | 20     | 21     | 22     |
| 2    | 23     | 24     | 25     | 26     | 27     | 28     | 29     |
| 3    | 1      | 2      | 3      | 4      | 5      | 6      | 7      |
| 3    | 8      | 9      | 10     | 11     | 12     | 13     | 14     |
| 3    | 15     | 16     | 17     | 18     | 19     | 20     | 21     |
| 3    | 22     | 23     | 24     | 25     | 26     | 27     | 28     |
| 3    | 29     | 30     | 31     | NULL   | NULL   | NULL   | NULL   |
| 4    | NULL   | NULL   | NULL   | 1      | 2      | 3      | 4      |
| 4    | 5      | 6      | 7      | 8      | 9      | 10     | 11     |
| 4    | 12     | 13     | 14     | 15     | 16     | 17     | 18     |
| 4    | 19     | 20     | 21     | 22     | 23     | 24     | 25     |
| 4    | 26     | 27     | 28     | 29     | 30     | NULL   | NULL   |
| 5    | NULL   | NULL   | NULL   | NULL   | NULL   | 1      | 2      |
| 5    | 3      | 4      | 5      | 6      | 7      | 8      | 9      |
| 5    | 10     | 11     | 12     | 13     | 14     | 15     | 16     |
| 5    | 17     | 18     | 19     | 20     | 21     | 22     | 23     |
| 5    | 24     | 25     | 26     | 27     | 28     | 29     | 30     |
| 5    | 31     | NULL   | NULL   | NULL   | NULL   | NULL   | NULL   |
| 6    | NULL   | 1      | 2      | 3      | 4      | 5      | 6      |
| 6    | 7      | 8      | 9      | 10     | 11     | 12     | 13     |
| 6    | 14     | 15     | 16     | 17     | 18     | 19     | 20     |
| 6    | 21     | 22     | 23     | 24     | 25     | 26     | 27     |
| 6    | 28     | 29     | 30     | NULL   | NULL   | NULL   | NULL   |
| 7    | NULL   | NULL   | NULL   | 1      | 2      | 3      | 4      |
| 7    | 5      | 6      | 7      | 8      | 9      | 10     | 11     |
| 7    | 12     | 13     | 14     | 15     | 16     | 17     | 18     |
| 7    | 19     | 20     | 21     | 22     | 23     | 24     | 25     |
| 7    | 26     | 27     | 28     | 29     | 30     | 31     | NULL   |
| 8    | NULL   | NULL   | NULL   | NULL   | NULL   | NULL   | 1      |
| 8    | 2      | 3      | 4      | 5      | 6      | 7      | 8      |
| 8    | 9      | 10     | 11     | 12     | 13     | 14     | 15     |
| 8    | 16     | 17     | 18     | 19     | 20     | 21     | 22     |
| 8    | 23     | 24     | 25     | 26     | 27     | 28     | 29     |
| 8    | 30     | 31     | NULL   | NULL   | NULL   | NULL   | NULL   |
| 9    | NULL   | NULL   | 1      | 2      | 3      | 4      | 5      |
| 9    | 6      | 7      | 8      | 9      | 10     | 11     | 12     |
| 9    | 13     | 14     | 15     | 16     | 17     | 18     | 19     |
| 9    | 20     | 21     | 22     | 23     | 24     | 25     | 26     |
| 9    | 27     | 28     | 29     | 30     | NULL   | NULL   | NULL   |
| 10   | NULL   | NULL   | NULL   | NULL   | 1      | 2      | 3      |
| 10   | 4      | 5      | 6      | 7      | 8      | 9      | 10     |
| 10   | 11     | 12     | 13     | 14     | 15     | 16     | 17     |
| 10   | 18     | 19     | 20     | 21     | 22     | 23     | 24     |
| 10   | 25     | 26     | 27     | 28     | 29     | 30     | 31     |
| 11   | 1      | 2      | 3      | 4      | 5      | 6      | 7      |
| 11   | 8      | 9      | 10     | 11     | 12     | 13     | 14     |
| 11   | 15     | 16     | 17     | 18     | 19     | 20     | 21     |
| 11   | 22     | 23     | 24     | 25     | 26     | 27     | 28     |
| 11   | 29     | 30     | NULL   | NULL   | NULL   | NULL   | NULL   |
| 12   | NULL   | NULL   | 1      | 2      | 3      | 4      | 5      |
| 12   | 6      | 7      | 8      | 9      | 10     | 11     | 12     |
| 12   | 13     | 14     | 15     | 16     | 17     | 18     | 19     |
| 12   | 20     | 21     | 22     | 23     | 24     | 25     | 26     |
| 12   | 27     | 28     | 29     | 30     | 31     | NULL   | NULL   |
+------+--------+--------+--------+--------+--------+--------+--------+

如果是舊版的 mysql 沒有遞迴可用時，底下這個醜醜的程式碼勉強可以跑個龍套，當然，這是抄襲自殺豬大的。

SELECT
	date_format( min( dt ), '%b' ) AS 'month',
	max( if(dow = 1 ,day(dt),'')) sun,
	max( if(dow = 2 ,day(dt),'')) mon,
	max( if(dow = 3 ,day(dt),'')) tue,
	max( if(dow = 4 ,day(dt),'')) wed,
	max( if(dow = 5 ,day(dt),'')) thu,
	max( if(dow = 6 ,day(dt),'')) fri,
	max( if(dow = 7 ,day(dt),'')) sat 
FROM (
	SELECT
		dt,
		dayofweek( dt ) dow,
		( @y := @y + IF ( dayofweek( dt ) = 1 or dayofmonth( dt ) = 1, 1, 0 ) ) AS week_no 
	FROM (		
			SELECT
				FROM_UNIXTIME(( @n := @n + 86400 ), '%Y-%m-%d' ) AS dt 
			FROM	information_schema.`TABLES`,
					( SELECT @n := UNIX_TIMESTAMP( date_format( sysdate(), '%Y-01-01' ))- 86400 ) AS s ,
					( SELECT @K := UNIX_TIMESTAMP(date_format(sysdate(), '%Y-12-31' ))) as p
			WHERE @n < @k		 
	) a , ( SELECT @y := 0 ) AS y 
	) b
GROUP BY week_no 
ORDER BY week_no;

Oracle

With total_date as (
    select trunc(sysdate,'YYYY')+level -1 DT from dual
    connect by trunc(sysdate,'YYYY')+ level < trunc(sysdate,'YYYY') + INTERVAL '1' YEAR
), fill_date as (
    select TO_CHAR(DT,'D') WEEK,TO_CHAR(DT,'IW') WTH, TO_CHAR(DT,'DD') DT, TRUNC(DT,'MM') MONTH from total_date
), pivot_date as( select * from fill_date
pivot(
    MAX(DT) FOR WEEK in (2 "星期一",3 "星期二",4 "星期三",5 "星期四",6 "星期五",7 "星期六",1 "星期日")
)
order by MONTH,WTH)
select TO_CHAR(MONTH,'MON') 月份, "星期一", "星期二","星期三","星期四","星期五","星期六", "星期日" from pivot_date;

MSSQL

CREATE FUNCTION [dbo].[CompareStrings] (@NewDate Nvarchar(max), @Num Int)
RETURNS Nvarchar(max)
BEGIN
  DECLARE @Str Nvarchar(max)
  SET @Str = ''
  IF LEFT(CONVERT(VARCHAR,DATEADD(DAY, @Num - DATEPART(WEEKDAY, @NewDate), @NewDate),120), 7) = LEFT(@NewDate, 7)
  BEGIN
    SET @Str = RIGHT(' ' + FORMAT(DATEADD(DAY, @Num - DATEPART(WEEKDAY, @NewDate), @NewDate), '%d'), 2)
  END
  RETURN @Str
END;

WITH CTE_GetDate AS (
SELECT CONVERT(DATE, '20200101') AS NewDate
UNION ALL
SELECT DATEADD(DAY, 1, NewDate)
FROM CTE_GetDate
WHERE DATEADD(DAY, 1, NewDate) <= CONVERT(DATE, '20201231'))
--
SELECT CASE WHEN LAG(Year_Month,1) OVER (PARTITION BY '' ORDER BY Year_Month) = Year_Month THEN '' ELSE Year_Month END AS 'Year_Month',
Sun,Mon,Tue,Wed,Thu,Fri,Sat
FROM (
SELECT LEFT(NewDate,7) AS 'Year_Month',
[dbo].CompareStrings(NewDate,1) AS 'Sun',
[dbo].CompareStrings(NewDate,2) AS 'Mon',
[dbo].CompareStrings(NewDate,3) AS 'Tue',
[dbo].CompareStrings(NewDate,4) AS 'Wed',
[dbo].CompareStrings(NewDate,5) AS 'Thu',
[dbo].CompareStrings(NewDate,6) AS 'Fri',
[dbo].CompareStrings(NewDate,7) AS 'Sat'
FROM CTE_GetDate
WHERE DatePart(DAY, NewDate)=1
OR DatePart(weekday , NewDate)=1
) AS TEMP
OPTION (MAXRECURSION 0)

Demo