Another Sequence Example

Here’s a little demonstration of the power of a sequence (or sometimes called Tally) table in SQL. The sequence table allows you to perform set logic for things that would normally require looping or procedural code.

Today I was working on a query that produced a set of possible values, and then used an outer join to present all implemented instances. The problem I had was that there were multiple groups, each having a different range of data for possible values. I needed a query presenting all possible values, and then all actual implementations.

Here is a demonstration using a contrived situation. Consider the letters A-Z in the alphabet. In the ANSII English character set these letters are defined by the ASCII values 65 – 91 consecutively. Let’s say I want to produce each letter, and count the number of characters from the beginning of the Alphabet to the current letter. For the first three characters it would look like:

A-1

B-1

B-2

C-1

C-2

C-3

…

Given the string ABCDEFGHIJKLMNOPQRSTUVWXYZ I want to parse each letter, and Count from 1 to the position of that character in the string. I’m going to use a sequence table twice. You may remember that a sequence table consists of one column with a number. It simply has a series of rows with an incrementing number. 1,2,3,4,5…

First, I’m going to use the sequence table to convert the string into a table, with one character per row, and the position in which that character was found in the string. I use a Common Table Expression (CTE) to do this transformation.

WITH Letters (Letter, Position)

AS

(

SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', Number, 1) , number

FROM sequence

WHERE NUMBER BETWEEN 1 AND 26

)

SELECT

Letter

,ASCII(Letter) as ASCValue

,'From 1 To ' + CONVERT(VARCHAR(2), Position) As Range

,Number

FROM Letters

JOIN Sequence ON Number BETWEEN 1 AND Position

The second use of the sequence table counts from 1 to the position of the current character in the string.

Here are the first three character results from the query:

Since my string contained the alphabet in character order, each new group increments by 1 from the previous group. However, you could easily sort the letters in a different order, resulting in a final output that has a more jagged appearence. The point is, the breakdown of each group is independent of the group before or after.

In this case the sequence table provides the power of a procedural loop without the overhead and complexity of the code.

Cheers,

Ben