* 가로 * 세로 (10 * 10), 지뢰가 20개인 Matrix 생성 쿼리

WITH TBL 
AS
( SELECT  LEVEL                                                                       AS NO
  ,       CEIL(LEVEL / 10)                                                            AS R
  ,       MOD(LEVEL - 1, 10) + 1                                                      AS C
  ,       DECODE(SIGN(ROW_NUMBER() OVER (ORDER BY DBMS_RANDOM.VALUE) - 21), -1, '*')  AS MINE
  FROM DUAL
  CONNECT BY LEVEL <= 10 * 10
)
SELECT  MIN(DECODE(T.C, 1,  T.MINE)) AS R1
,       MIN(DECODE(T.C, 2,  T.MINE)) AS R2
,       MIN(DECODE(T.C, 3,  T.MINE)) AS R3
,       MIN(DECODE(T.C, 4,  T.MINE)) AS R4
,       MIN(DECODE(T.C, 5,  T.MINE)) AS R5
,       MIN(DECODE(T.C, 6,  T.MINE)) AS R6
,       MIN(DECODE(T.C, 7,  T.MINE)) AS R7
,       MIN(DECODE(T.C, 8,  T.MINE)) AS R8
,       MIN(DECODE(T.C, 9,  T.MINE)) AS R9
,       MIN(DECODE(T.C, 10, T.MINE)) AS R10
FROM  ( SELECT  R
        ,       C
        ,       NVL2(MINE,  MINE
                         , (SELECT SUM(DECODE(D.MINE, '*', 1))
                              FROM TBL D
                             WHERE (D.R BETWEEN T.R - 1 AND T.R + 1) 
                               AND (D.C BETWEEN T.C - 1 AND T.C + 1))
                         ) AS MINE
        FROM TBL T) T
GROUP BY T.R
;
SQL