用 TSQL CTE 跑遞迴，參考看看

CREATE TABLE Temp (
	menu smallint , 
	option_type char(1) , 
	option_val char(1));

INSERT INTO Temp VALUES
(1, 'A', '1') , (1, 'A', '2'),
(2, 'A', '1') , (2, 'A', '2'), (2, 'B', '1'), (2, 'B', '2'),
(3, 'A', '1') , (3, 'A', '2') , (3, 'B', '1') , (3, 'B', '2') , (3, 'C', '1') , (3, 'C', '2');

; 
WITH CTE AS
(
	SELECT 
		menu , 
		option_type , 
		option_val , 
		CAST(option_type + option_val AS varchar(8000)) AS Memo ,
		CAST(1 as int) AS Stat
	FROM Temp
	UNION ALL
	SELECT 
		T2.menu , 
		T2.option_type , 
		T2.option_val ,
		T1.Memo + '_' + T2.option_type + T2.option_val ,
		CAST(Stat + 1 as int) AS Stat
	FROM Temp AS T2
		JOIN CTE AS T1 ON T2.menu = T1.menu 
							AND T2.option_type > T1.option_type
)
SELECT 
	F1.* 
FROM CTE AS F1
	JOIN
		(
			SELECT 
				menu , 
				COUNT(DISTINCT option_type) AS Stat
			FROM Temp
			GROUP BY menu 
		) AS F2 ON F1.menu = F2.menu 
					AND F1.stat = F2.stat
ORDER BY menu , memo

DROP TABLE IF EXISTS Temp

TSQL 語法連結

-- 查詢結果
SELECT
    t1.menu,
    CONCAT(t1.option_type, t1.option_val) AS menu_desc
FROM
    your_table t1
WHERE
    t1.option_type = 'A' and t1.menu = 1

UNION

SELECT
    t1.menu,
    CONCAT(t1.option_type, t1.option_val, ',', t2.option_type, t2.option_val) AS menu_desc
FROM
    your_table t1
JOIN your_table t2 ON t1.menu = t2.menu AND t1.option_type = 'A' AND t2.option_type = 'B'
WHERE t1.menu = 2

UNION

SELECT
    t1.menu,
    CONCAT(t1.option_type, t1.option_val, ',', t2.option_type, t2.option_val, ',', t3.option_type, t3.option_val) AS menu_desc
FROM
    your_table t1
JOIN your_table t2 ON t1.menu = t2.menu AND t1.option_type = 'A' AND t2.option_type = 'B'
JOIN your_table t3 ON t2.menu = t3.menu AND t2.option_type = 'B' AND t3.option_type = 'C'
ORDER BY
    menu,
    menu_desc;

按我前往

引用pilipala的SQL程式碼
配合版主的結果顯示XD....

;WITH CTE AS
(
	SELECT 
		menu , 
		option_type , 
		option_val , 
		CAST(option_type + option_val AS varchar(8000)) AS Memo ,
		CAST(1 as int) AS Stat
	FROM Temp
	UNION ALL
	SELECT 
		T2.menu , 
		T2.option_type , 
		T2.option_val ,
		T1.Memo 
        + (case when Stat + 1 = 3 then ': ' else ' ' end)
        + T2.option_type + T2.option_val ,
		CAST(Stat + 1 as int) AS Stat
	FROM Temp AS T2
		JOIN CTE AS T1 ON T2.menu = T1.menu 
							AND T2.option_type > T1.option_type
)
SELECT F1.menu
,F1.Memo [desc]
FROM CTE AS F1
	JOIN
		(
			SELECT 
				menu , 
				COUNT(DISTINCT option_type) AS Stat
			FROM Temp
			GROUP BY menu 
		) AS F2 ON F1.menu = F2.menu 
					AND F1.stat = F2.stat
ORDER BY menu , memo

https://dbfiddle.uk/e_Q1P-QF