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

**R – TomazTsql**, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)

Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

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.

### Cr**eate 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:

**it would return value**

*get_grid 2,3***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!

**leave a comment**for the author, please follow the link and comment on their blog:

**R – TomazTsql**.

R-bloggers.com offers

**daily e-mail updates**about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.

Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.