StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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 .</p> <p>The 'With CTE' effectively creates an in-memory table named CTE, so this new 3-column, 81-row table is called CTE.</p> <p>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.</p> <p>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?</p> <p>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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload