SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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'),
(4, 'A', '1'),
(4, 'A', '2');
14 rows affected

SELECT * FROM Temp


menu
option_type
option_val


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

4
A
1

4
A
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


menu
option_type
option_val
Memo
Stat


1
A
1
A1
1

1
A
2
A2
1

2
B
1
A1_B1
2

2
B
2
A1_B2
2

2
B
1
A2_B1
2

2
B
2
A2_B2
2

3
C
1
A1_B1_C1
3

3
C
2
A1_B1_C2
3

3
C
1
A1_B2_C1
3

3
C
2
A1_B2_C2
3

3
C
1
A2_B1_C1
3

3
C
2
A2_B1_C2
3

3
C
1
A2_B2_C1
3

3
C
2
A2_B2_C2
3

4
A
1
A1
1

4
A
2
A2
1

menu	option_type	option_val	Memo	Stat
1	A	1	A1	1
1	A	2	A2	1
2	B	1	A1_B1	2
2	B	2	A1_B2	2
2	B	1	A2_B1	2
2	B	2	A2_B2	2
3	C	1	A1_B1_C1	3
3	C	2	A1_B1_C2	3
3	C	1	A1_B2_C1	3
3	C	2	A1_B2_C2	3
3	C	1	A2_B1_C1	3
3	C	2	A2_B1_C2	3
3	C	1	A2_B2_C1	3
3	C	2	A2_B2_C2	3
4	A	1	A1	1
4	A	2	A2	1