Database Bejewelled Puzzle- How does this solution work?

Question

I'm interested in the programming challenge presented by the game Bejewelled. It seems like a simple game but programmatically it's more complex that it looks.

In my search for hints on how the board is evaluated, I came across this QUIZ put on by the good folks at Simple-Talk. They have posted the winning answer, but I'm tarred and feathered if I can really grok how the solution works. I can see that it has something to do with matrices and grouping the cell values together with their rows and columns, but that's as far as I have gotten so far. Can anyone break it down a little further for me?

POSTED SOLUTION (the details of the quiz are at the link above):

--====== Table matches needs to be loaded only once
CREATE TABLE matches(offsetRow1 INT, offsetCol1 INT, offsetRow2 INT, ofsetCol2 INT, directions VARCHAR(20))
-- for horizontal 
INSERT INTO matches VALUES(-1, -1, -1, -2, 'up')
INSERT INTO matches VALUES(-1, -1, -1, 1, 'up')
INSERT INTO matches VALUES(-1, 1, -1, 2, 'up')        
INSERT INTO matches VALUES( 1, -1, 1, -2, 'down')           
INSERT INTO matches VALUES( 1, -1, 1, 1, 'down')
INSERT INTO matches VALUES( 1, 1, 1, 2, 'down')       
INSERT INTO matches VALUES( 0, -2, 0, -3, 'left')     
INSERT INTO matches VALUES( 0, 2, 0, 3, 'right')            
-- for verical
INSERT INTO matches VALUES(-2, -1, -1, -1, 'left')
INSERT INTO matches VALUES(-1, -1, 1, -1, 'left')
INSERT INTO matches VALUES( 1, -1, 2, -1, 'left')
INSERT INTO matches VALUES(-2, 1, -1, 1, 'right')
INSERT INTO matches VALUES(-1, 1, 1, 1, 'right')
INSERT INTO matches VALUES( 1, 1, 2, 1, 'right')
INSERT INTO matches VALUES(-2, 0, -3, 0, 'up')
INSERT INTO matches VALUES( 2, 0, 3, 0, 'down')

--==================================================
;WITH CTE
      AS
  (
  SELECT
        [Row] = CAST( [#] AS INT ),
        [Col] = CAST( [Col] AS INT ),
        [Value]
    FROM bejeweled
        UNPIVOT ([Value] FOR [Col] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9])) unpvt
  )
SELECT DISTINCT T.Row, T.Col, T.Value, directions
  FROM CTE T
      JOIN CTE T1
      ON T.Value = T1.Value
      JOIN CTE T2
      ON T.Value = T2.Value
      JOIN matches
      ON (T1.Row - T.Row) = offsetRow1
    AND (T1.Col - T.Col) = offsetCol1
    AND (T2.Row - T.Row) = offsetRow2
    AND (T2.Col - T.Col) = ofsetCol2
  ORDER BY T.Row, T.Col

Answer 1

The unpivot function turns the original data into a linear list rather than a matrix. For example, since the value at 1,1 in the original column was 8, the first row of the new table is 1,1,8 . Likewise, since the second column of the first row of the original table was a 5, the second row of our new table is 1,2,5 .

The 'With CTE' effectively creates an in-memory table named CTE, so this new 3-column, 81-row table is called CTE.

The logic happens with the inner join: every cell in CTE gets joined to every cell in CTE where the values match, and again with itself where the values match. This means that every cell in the original table knows of every other possible three-item match. That is, every single permutation of a list of 3 cells containing value '1' (for example) are returned.

Let's look at the value 2. There is one at (6,2), another at (5,3) and another at (7,3) , so one of the possible values of the inner join would have T.Row be 6, T.Col be 2, T1.Row be 5, T1.Col be 3, T2.Row be 7, and T2.Col be 3. We know by looking at it that swapping (6,2) with (6,3) would put the three in a row. But how does the JOIN statement know?

Well, a valid move is one that puts T in between T1 and T2. The easiest way to determine if our combination of 3 meets that is by checking the offsets and comparing that to a list of relative positions that work. T1 is above and to the right of T (-1,1), and T2 is below and to the right of T (1,1). We check and see if (-1,1,1,1) is a valid match. It is, so it passes the JOIN criteria and is kept as a result.