Facebook Sign in | Join

			5 MINUTES to source control your database
Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

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

Share

Comments

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

Monday, May 17, 2010 8:30 AM by chris_kulisz

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

Monday, May 17, 2010 9:00 AM by Jacob Sebastian

@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

Monday, May 17, 2010 9:14 AM by Peso

"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

Tuesday, May 18, 2010 3:19 PM by NetDefender

Easy


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

Tuesday, May 18, 2010 8:03 PM by MisterMagoo

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

Thursday, May 20, 2010 3:41 AM by uzairahm

@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

Monday, May 31, 2010 11:27 AM by jcelko

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

Thursday, June 03, 2010 4:24 PM by Kamen Angelov

:) 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

Monday, June 14, 2010 9:15 AM by jcelko

>> 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.


Copyright © Rivera Informatic Private Ltd.