Python and R have matrix and simple loops. What can you use in T-SQL?

This article is originally published at https://tomaztsql.wordpress.com

Many of you have already used any programming or scripting language. And you can tell that, there are some functionalities that are intuitive (for loops, enumerations, collections, …), data types and also short-hands.

One of these data types is a matrix. Commonly used in a programming language, yet fairly unknown in T-SQL. You can say, that matrix is a list of lists or an array of arrays. Or simply a table

Scripting languages offer a super simple solution to walk through a matrix. Using enumerators or loops, you can check each element using a simple loop. For example with python code:

for i in range(0,3):
    for j in range(0,3):
        print(grid[i][j])

And you can even add some logic. For example, if the value equals a predefined value then print the correct result.

for i in range(0,3):
    for j in range(0,3):
        if grid[i][j] == 23:
            print("true", i, j)
        print("not exists", i, j)

And in this case, you will get a simple result print:

The same operation can be simply created also in R.

mat = matrix(c(12,11,23,23,34,35,51,55,56),nrow = 3, ncol = 3,byrow = TRUE)
print(mat)

for (i in 1:3){
  for (j in 1:3){
    print(mat[i,j])
  }
}

And with IF statement embedded.

for (i in 1:3){
  for (j in 1:3){
    if (mat[i,j] == 23){
      print(paste("true", i, j, sep = " "))
    } else {
      print (paste("not exists", i, j, sep = " "))
    }
  }
}

It is obvious, that both languages are delivering these operations with little syntax and very efficiently.

As you can imagine, doing such an operation in any other scripting or programming language would not take much more additional programming and would be fairly similar.

Why would this operation be so annoying to write in T-SQL? Tables in SQL Server are columns with values in rows. And columns are usually presented in select list, where as rows, are filtered in WHERE clause. So combining these two query parts is somehow upside down, as e.g.: in this python code. Meaning, that looping through rows should not be a problem – because this is the basic logic of SQL – but looping through columns is something that is usually defined and is not part of dynamic query or last second wishlist. When writing a query, upfront user knows, what information (column) would like to retrieve from the table.

So we want to slightly change this logic. And you ask why? Well, because of simpler rows and columns manipulation.

Create an SQL table

Let’s create an SQL Table and store same data as in previous Python matrix:

DROP TABLE IF EXISTS dbo.grid2;
CREATE TABLE dbo.grid2
( ID TINYINT IDENTITY(1,1)
, [1] TINYINT NOT NULL
, [2] TINYINT NOT NULL
, [3] TINYINT NOT NULL)


INSERT INTO dbo.grid2 ([1],[2],[3])
                      SELECT 12,11,23
UNION ALL SELECT 23,34,35
UNION ALL SELECT 51,55,56

The result of simple select statement against this table is:

Procedure

For the sake of sanity, I “cheated” here Adding an indexing column ID for simpler SQL data manipulations and column names are enumerated as [1], [2], [3]. I could have used letters or any ASCII letter that would be part of ASCII series. e.g.: ASCII(50), ASCII(51), ASCII(52), …. so that the integers can be simply increased or decreased.

Ideally, we are looking for a T-SQL notation: get_grid(row, column) that would return a single value. For example: get_grid 2,3 it would return value 35 (based on data in table; row=2, column=3).

We want this function to be clean and simple to use with further functions or procedures.

After couple of iterations

CREATE OR ALTER PROCEDURE dbo.get_grid
(@row TINYINT
,@col TINYINT
,@result TINYINT OUTPUT
)
AS
BEGIN
	DECLARE @sql NVARCHAR(1000)
	SET @sql = '
		SELECT 	
			 '+CAST(QUOTENAME(@col) as VARCHAR(100))+' 
		FROM dbo.grid2 as g
		WHERE
			g.ID = '+ CAST(@row AS VARCHAR(100)) +' 
	'

	DECLARE @t table (i tinyint)
	INSERT INTO @t
	EXEC sp_executesql @sql

	SET @result = (SELECT i FROM @t)
	 
END;
GO

So calling this procedure with output parameter would look like:

DECLARE @v TINYINT; 
EXEC dbo.get_grid 2,3, @v OUT
SELECT @v

Running double loop or nested loop

Now pretend that the table is a matrix, and we are looping through the matrix:

DECLARE @i INT = 1
WHILE @i <= 3
    BEGIN
    DECLARE @j INT = 1
    WHILE @j <= 3
        BEGIN
                DECLARE @v  TINYINT  = 0
                EXEC dbo.get_grid
                    @i
                    ,@j
                    ,@v OUT
                
                IF (@v = 23) 
                    SELECT 'True', @i, @j, @v
                ELSE
                    SELECT 'Not Exists', @i, @j, @v
        SET @j = @j + 1
        END
SET @i = @i + 1 
END

We get the same results as with Python, with slightly more overhead code.

Now, you will ask. But why? Well, this is just a helper T-SQL function for easier board-game development in T-SQL, where there are matrix numbers needed.

As always, complete code is available at Github repository for the Sudoku T-SQL game.

Stay healthy!

Thanks for visiting r-craft.org
This article is originally published at https://tomaztsql.wordpress.com
Please visit source website for post related comments.