SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE T$TICKET ( TICKET_GAME NUMBER NOT NULL, TICKET_MULTIPLIER NUMBER NOT NULL, TICKET_ID NUMBER NOT NULL PRIMARY KEY ); INSERT INTO T$TICKET (TICKET_GAME, TICKET_MULTIPLIER, TICKET_ID) VALUES (28101, 1, 3451363512); INSERT INTO T$TICKET (TICKET_GAME, TICKET_MULTIPLIER, TICKET_ID) VALUES (5150, 1, 3451443647); INSERT INTO T$TICKET (TICKET_GAME, TICKET_MULTIPLIER, TICKET_ID) VALUES (5150, 1, 3466861537); INSERT INTO T$TICKET (TICKET_GAME, TICKET_MULTIPLIER, TICKET_ID) VALUES (5101, 1, 3466851542); INSERT INTO T$TICKET (TICKET_GAME, TICKET_MULTIPLIER, TICKET_ID) VALUES (5101, 1, 3466861702); INSERT INTO T$TICKET (TICKET_GAME, TICKET_MULTIPLIER, TICKET_ID) VALUES (5101, 1, 3466861707); INSERT INTO T$TICKET (TICKET_GAME, TICKET_MULTIPLIER, TICKET_ID) VALUES (5150, 1, 3467829272); CREATE TABLE T$MASK ( MASK VARCHAR2(4000) NOT NULL, NMBR NUMBER NOT NULL, TICKET_ID NUMBER NOT NULL, PRIMARY KEY (TICKET_ID, NMBR) ); INSERT INTO T$MASK (MASK, NMBR, TICKET_ID) VALUES ('4,13,17,21,24,26;', 1, 3466861542); INSERT INTO T$MASK (MASK, NMBR, TICKET_ID) VALUES ('2,6,19,22,23,25;', 1, 3466861702); INSERT INTO T$MASK (MASK, NMBR, TICKET_ID) VALUES ('11,17,19,32,33,42;', 1, 3466861707); INSERT INTO T$MASK (MASK, NMBR, TICKET_ID) VALUES ('11,13,23,26,32,35,43;', 1, 3451443647); INSERT INTO T$MASK (MASK, NMBR, TICKET_ID) VALUES ('2,3,21,22,24,33,44;', 4, 3451443647); INSERT INTO T$MASK (MASK, NMBR, TICKET_ID) VALUES ('3,15,24,34,39,40,48;', 1, 3466861537); INSERT INTO T$MASK (MASK, NMBR, TICKET_ID) VALUES ('4,13,17,21,31,33,41;', 3, 3467829272); INSERT INTO T$MASK (MASK, NMBR, TICKET_ID) VALUES ('1,15,21,26,29,39,42;', 4, 3467829272); INSERT INTO T$MASK (MASK, NMBR, TICKET_ID) VALUES ('3,7,11,16,25,33,43;', 1, 3467829272); INSERT INTO T$MASK (MASK, NMBR, TICKET_ID) VALUES ('2,3,4,6,13,15,16,17,19,20,21,22;', 2, 3451363512); SELECT T.TICKET_ID, T.TICKET_GAME, T.TICKET_MULTIPLIER, LISTAGG(CAST(G.N AS VARCHAR2(4000)) || ':' || COALESCE(M.MASK, ';'), '') WITHIN GROUP (ORDER BY G.N) COMBINATION FROM T$TICKET T CROSS JOIN ( SELECT ROWNUM N FROM DUAL CONNECT BY ROWNUM <= 256 ) G -- Generator from 1 to 256 LEFT OUTER JOIN T$MASK M ON M.NMBR = G.N AND M.TICKET_ID = T.TICKET_ID GROUP BY T.TICKET_ID, T.TICKET_GAME, T.TICKET_MULTIPLIER;