## TSQL Challenge 30 - A Matrix Transposition challenge in TSQL

This is a matrix transposition challenge and your job is to change the position of the numbres in a 5x5 matrix.

http://beyondrelational.com/puzzles/challenges/34/a-matrix-transposition-challenge-in-tsql.aspx

## # re: TSQL Challenge 30 - A Matrix Transposition challenge in TSQL

In the expected outputs you have some errors in the example.

col1does not match row 6 (3,1,9,7,8) -> (1,3,5,6,8) : 9,7 and 5,6

col2 does not match row 5 ( 5,4,1,8,3) -> (1,3,4,5,7) : 8 and 7

col3 matches output row 3

col4 matches output row 4

col5 does not match output row 7 (6,7,3,5,4) -> (3,4,7,8,9) : 9 and 6

## # re: TSQL Challenge 30 - A Matrix Transposition challenge in TSQL

@chris,

if you follow the three instructions about the transposition required, you will get the exact output given in the challenge. Please take another look and let me know.

## # re: TSQL Challenge 30 - A Matrix Transposition challenge in TSQL

"After transforming each row to columns, sort each columns individually. The content of each column should be sorted smallest to largest "

Should read

"After transforming each column to row, sort each columns individually. The content of each column should be sorted smallest to largest "

Row -> Column vs Column -> Row

## # re: TSQL Challenge 30 - A Matrix Transposition challenge in TSQL

Easy

## # re: TSQL Challenge 30 - A Matrix Transposition challenge in TSQL

Nice challenge that has shown me a few things about stats that I didn't know.

## # re: TSQL Challenge 30 - A Matrix Transposition challenge in TSQL

@Chris what i thought how the transformation would go is like below:

3 1 9 7 8

5 4 1 8 3

1 0 5 2 2

4 3 6 1 1

6 7 3 5 4

1 3 7 8 9

1 3 4 5 8

0 1 2 2 5

1 1 3 4 6

3 4 5 6 7

0 1 2 2 5

1 1 3 4 6

1 3 4 5 7

1 3 5 6 8

3 4 7 8 9

Hope this will help....

## # re: TSQL Challenge 30 - A Matrix Transposition challenge in TSQL

Just a quick thought before I have to get back to doing animal rescue over the holiday weekend(my wife volunteered me). This matrix problem is much easier if you use a relational design and have Standard SQL.

The traditional model for a matrix in math and computer is to have i-rows and j-columns. I use these terms to avoid confusing with rows and columns in the SQL sense.

If you remember FORTRAN, the reason for starting the subscripts with these letters is that this makes them INTEGER automatically. i have no idea why this is a tradition in math.

How is a matrix implemented internally in a procedural would look like this in SQL:

CREATE TABLE Matrix

(i INTEGER NOT NULL

CHECK(i BETWEEN 1 AND 5),

j INTEGER NOT NULL

CHECK(j BETWEEN 1 AND 5),

PRIMARY KEY(i, j),

val INTEGER NOT NULL);

The Transpose is simple:

UPDATE Matrix

SET i = j, j = i;

With Standard SQL, Ordinal functions are functions and can be used anywhere. T-SQL still has restrictions, unfortunately. Sorting the i-rows and j-columns is easy; partition by the j-columns(i-rows) then sort each one by the val, and update the i-row (j-column).

UPDATE Matrix

SET i = ROW_NUMBER() OVER(PARTITION BY j ORDER BY val);

UPDATE Matrix

SET j = ROW_NUMBER() OVER(PARTITION BY i ORDER BY val);

You can nest them together in a VIEW or whatever to get the final answer.

## # re: TSQL Challenge 30 - A Matrix Transposition challenge in TSQL

:) Hey jcelko , read before think, think before write!

If you needs FORTRAN IV or ALGOL-61 solution i can supply it for you, but the problem is to write select statement. If you are assigned to DENYWRITER role but have SELECT permitions on the table...

## # re: TSQL Challenge 30 - A Matrix Transposition challenge in TSQL

>> If you needs FORTRAN IV or ALGOL-61 solution I can supply it for you, <<

There was never an Algol-61; we had Algol-60 and Algol-68 (a real disaster of a super-language). Fortran 2003 was published 2004 November 18. I follow all the J3 languages since they can host SQL.

>> but the problem is to write select statement. <<

I did it with UPDATES, so pieces could be tested. Use CTEs to put them together:

WITH Transpose (i, j, val)

AS

(SELECT j AS i, i AS j, val FROM Matrix),

Col_Sort (i, j, val)

AS

(SELECT ROW_NUMBER() OVER(PARTITION BY j ORDER BY val)AS i, j, val

FROM Transpose),

Row_Sort (i, j, val)

AS

(SELECT i, ROW_NUMBER() OVER(PARTITION BY i ORDER BY val) AS j, val

FROM Col_Sort)

SELECT i, j, val FROM Row_Sort;

Again, this is an ANSI/ISO solution. T-SQL does not like the column list, and limits where ordinal functions can be used. The ordinals can be replaced with some ugly looking self-joins.

The real point I wanted to make is that good DDL, with the proper constraints, etc. make the DML so much easier. The original version picked the worst way to model a matrix in SQL. It shows the common Newbie confusion of tables with spreadsheets. Just because they look alike on the screen, does not make them the same thing.