CREATE TABLE tab (
   exdate date
 , vendor char(2)
 , product Nvarchar(20)
);

INSERT INTO tab values
('2021-08-01', '01', N'蘋果'),
('2021-08-02', '01', N'芭樂'),
('2021-08-03', '01', N'水蜜桃'),
('2021-09-01', '01', N'蘋果'),
('2021-09-02', '01', N'西瓜'),
('2021-09-03', '01', N'香蕉'),
('2021-08-05', '01', N'番茄'),
('2021-08-01', '02', N'蘋果'),
('2021-08-02', '02', N'芭樂'),
('2021-08-03', '02', N'水蜜桃');

解法一 :

;WITH CTE_01 AS (
SELECT 
ROW_NUMBER() OVER (PARTITION BY vendor,CONVERT(varchar(6), exdate, 112) ORDER BY exdate) AS NO,*
FROM tab
),
CTE_02 AS (
SELECT exdate,vendor,product,CONVERT(varchar(6), exdate, 112) AS exdatePART
FROM CTE_01
WHERE NO=1
),
CTE_03 AS (
SELECT exdate,vendor,product,CONVERT(varchar(6), exdate, 112) AS exdatePART
FROM CTE_01
WHERE NO=2
)

SELECT A.exdatePART,A.vendor,A.product,B.product
FROM CTE_02 AS A
LEFT JOIN CTE_03 AS B ON A.exdatePART = B.exdatePART AND A.vendor = B.vendor

Demo

解法二 A :

SELECT LEFT(REPLACE(exdate,'-',''),6) AS YearMonth,vendor,product,product2
FROM (SELECT *,
ROW_NUMBER() OVER (PARTITION BY vendor,LEFT(REPLACE(exdate,'-',''),6) ORDER BY exdate) AS NO,
LEAD(product,1,'') OVER (PARTITION BY vendor,LEFT(REPLACE(exdate,'-',''),6) ORDER BY exdate) AS product2
FROM tab) M
WHERE M.NO = 1

Demo (MsSQL 2017)
Demo (MySQL 8.0)

解法二 B :

SELECT to_char(exdate,'YYYYMM') AS YearMonth,vendor,product,product2
FROM (SELECT exdate,vendor,product,
ROW_NUMBER() OVER (PARTITION BY vendor,to_char(exdate,'YYYYMM') ORDER BY exdate) AS NO,
LEAD(product,1,'') OVER (PARTITION BY vendor,to_char(exdate,'YYYYMM') ORDER BY exdate) AS product2
FROM tab) M
WHERE M.NO = 1

Demo (Oracle 11)

check

select * from (
    select 
      rank() over(partition by vendor,trunc(exdate,'mm') order by exdate) rnk,
      product,
      to_char(exdate,'YYYYMM') month,
      vendor 
    from tab t
) pivot
(
    max(product) for rnk in (1 "水果1",2 "水果2") 
)
order by 2,1;

不考慮重覆喔XD

SELECT ym,vendor,SUBSTRING_INDEX(GROUP_CONCAT(product),",",2) FROM (
	SELECT *,DATE_FORMAT(exdate,"%Y%m") AS ym FROM `tab` ORDER BY exdate 
) AS t group BY ym,vendor

沒注意到題意有需要 vendor
改了一下

還沒實測過...

SELECT DATE_FORMAT(tab.exdate,"%Y%m") AS `銷售年月`,
        tab.vendor AS `供應商id`,
        SUBSTRING_INDEX(GROUP_CONCAT(tab.product),",",1) AS `水果1`,
        SUBSTRING_INDEX(GROUP_CONCAT(tab.product),",",2) AS `水果2`
FROM tab
WHERE (
        SELECT COUNT(*)
        FROM tab ci
        WHERE YEAR(tab.exdate) = YEAR(ci.exdate)
          AND MONTH(tab.exdate) = MONTH(ci.exdate)
          AND tab.vendor = ci.vendor
          AND tab.exdate > ci.exdate
    ) < 2

GROUP BY YEAR(tab.exdate), MONTH(tab.exdate), tab.vendor

基本上1,2不可能重複啊...

TSQL

SELECT *
FROM
	(
		SELECT 
			CONVERT(char(6) , exDate , 112) AS YYMM ,
			vendor ,
			product ,
			ROW_NUMBER() OVER (PARTITION BY CONVERT(char(6) , exDate , 112) , vendor ORDER BY (SELECT NULL)) AS RowNO
		FROM tab
	) AS T
	PIVOT
	(
		MIN(product) FOR RowNO IN ([1] , [2])
	) AS PV