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 |