{"id":470,"date":"2008-11-15T00:00:00","date_gmt":"2008-11-15T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/sql-server-matrix-workbench\/"},"modified":"2021-09-29T16:22:08","modified_gmt":"2021-09-29T16:22:08","slug":"sql-server-matrix-workbench","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/sql-server-matrix-workbench\/","title":{"rendered":"SQL Server Matrix Workbench"},"content":{"rendered":"

I think that we need to lay to rest the idea that you cannot do matrix operations in SQL. They are just as easy as they are in a procedural language. One just needs a clear head and think in terms of set-based operations.<\/p>\n

If you’re not aware of the usefulness of matrix operations, then you’ll be surprised. They underlie a lot of linear equations and statistics (We do a cool Orthogonal Factor Analysis in SQL!). They’re used a lot in games programming, and bitmap manipulation. We tend to use them for sticky SQL problems such as reserving seats or timetabling. Once you’ve started, you’ll find a lot of SQL problems that can be solved with matrices. (Just in case the formatting goes awry, we’re including the SQL Source file in the downloads at the bottom of the article.) Please remember that this isn’t production-quality code: as in all of our workbenches, we’re keeping things simple to illustrate the points as clearly as we can.<\/p>\n

We’ll create a table to store two-dimensional arrays that contain an exact number with two digits precision. You’ll see that we can create any number of matrixes in this array, which cuts down on the chore of creating tables. It also means that functions and procedures can be hardwired to a common matrix table. Of course, you would create the ‘element’ type according to your data.<\/p>\n

     IF   OBJECT_ID   (  N'matrix'  )   IS   NOT NULL \r\n        DROP TABLE   matrix \r\n     CREATE TABLE   matrix   ( \r\n            [Name]   VARCHAR  (  10  ), \r\n            x   INT   NOT NULL, \r\n            y   INT   NOT NULL, \r\n            element NUMERIC  (  9  ,  2  ) NOT NULL  \r\n            PRIMARY KEY   (  [name]  ,  x  ,  y  )) \r\n            <\/pre>\n
Yes, this allows the use of sparse arrays, but you would usually need to handle gaps. Note the compound primary key. This automatically prevents duplication of data in a cell (try it in order to convice yourself) and provides a reasonable index for most operations. If you use very large matrixes, you will need to provide an extra covering index.<\/p>\n
Now let’s fill matrix ‘A’ with some spoof data so we can just do some simple stuff such as summing the rows and columns.<\/p>\n
We’ll choose a ten by fifteen matrix:<\/p>\n
     SET NOCOUNT ON  --cut down on the badinage \r\n     DELETE FROM   matrix   WHERE   [Name]  =  'A' \r\n     DECLARE   @ii   INT  ,   @jj   INT \r\n    SELECT  @ii  =  1  ,   @jj  =  1 \r\n     WHILE   @ii  <=  10  -- loop around as if we were C# programmers! \r\n        BEGIN \r\n       WHILE  @jj  <=  15 \r\n            BEGIN \r\n           INSERT INTO  matrix   SELECT   'A'  ,  @ii  ,  @jj  ,  RAND  ()*  200 \r\n            SELECT   @jj  =  @jj  +  1 \r\n            END \r\n       SELECT  @jj  =  1  ,   @ii  =  @ii  +  1 \r\n        END \r\n        <\/pre>\n
Now the simple way to do a summation is by using the built-in grouping functions such as ROLLUP<\/code> and CUBE<\/code>:<\/p>\n
     SELECT   [X\/Y]  =  CASE   WHEN   GROUPING  (  y  )  =  0  \r\n                        THEN   CAST  (  y   AS VARCHAR  (  6  ))  \r\n                        ELSE   'Sum'  \r\n                  END  , \r\n            '1'  =  SUM  (  CASE   WHEN   x  =  1   THEN   element   ELSE   0   END  ),  \r\n            '2'  =  SUM  (  CASE   WHEN   x  =  2   THEN   element   ELSE   0   END  ),  \r\n            '3'  =  SUM  (  CASE   WHEN   x  =  3   THEN   element   ELSE   0   END  ),  \r\n            '4'  =  SUM  (  CASE   WHEN   x  =  4   THEN   element   ELSE   0   END  ),  \r\n            '5'  =  SUM  (  CASE   WHEN   x  =  5   THEN   element   ELSE   0   END  ),  \r\n            '6'  =  SUM  (  CASE   WHEN   x  =  6   THEN   element   ELSE   0   END  ),  \r\n            '7'  =  SUM  (  CASE   WHEN   x  =  7   THEN   element   ELSE   0   END  ),  \r\n            '8'  =  SUM  (  CASE   WHEN   x  =  8   THEN   element   ELSE   0   END  ),  \r\n            '9'  =  SUM  (  CASE   WHEN   x  =  9   THEN   element   ELSE   0   END  ),  \r\n            '10'  =  SUM  (  CASE   WHEN   x  =  10   THEN   element   ELSE   0   END  ),  \r\n            'total'  =  SUM  (  element  ) \r\n     FROM   matrix   WHERE   [name]  =  'a' \r\n     GROUP BY   y   WITH   ROLLUP   ORDER BY   GROUPING  (  y  ),  y  \r\n    <\/pre>\n
\"600-First.jpg\"<\/p>\n
Note that, if you are using sparse arrays, you may need to do this.<\/p>\n
First, we’ll put in a few drips and drabs of data.<\/p>\n
     DELETE FROM   matrix   WHERE   [name]  =  'z' \r\n     INSERT INTO   matrix   SELECT   'z'  ,  3  ,  5  ,  RAND  ()*  200 \r\n     INSERT INTO   matrix   SELECT   'z'  ,  7  ,  8  ,  RAND  ()*  200 \r\n     INSERT INTO   matrix   SELECT   'z'  ,  2  ,  3  ,  RAND  ()*  200 \r\n    \r\n     SELECT   [X\/Y]  =  CASE   WHEN   GROUPING  (  ydimension.number  )  =  0  \r\n     THEN   CAST  (  ydimension.number   AS VARCHAR  (  6  ))  \r\n     ELSE   'Sum'  \r\n     END  , \r\n     '1'  =  SUM  (  CASE   WHEN   x  =  1   THEN   element   ELSE   0   END  ),  \r\n     '2'  =  SUM  (  CASE   WHEN   x  =  2   THEN   element   ELSE   0   END  ),  \r\n     '3'  =  SUM  (  CASE   WHEN   x  =  3   THEN   element   ELSE   0   END  ),  \r\n     '4'  =  SUM  (  CASE   WHEN   x  =  4   THEN   element   ELSE   0   END  ),  \r\n     '5'  =  SUM  (  CASE   WHEN   x  =  5   THEN   element   ELSE   0   END  ),  \r\n     '6'  =  SUM  (  CASE   WHEN   x  =  6   THEN   element   ELSE   0   END  ),  \r\n     '7'  =  SUM  (  CASE   WHEN   x  =  7   THEN   element   ELSE   0   END  ),  \r\n     '8'  =  SUM  (  CASE   WHEN   x  =  8   THEN   element   ELSE   0   END  ),  \r\n     '9'  =  SUM  (  CASE   WHEN   x  =  9   THEN   element   ELSE   0   END  ),  \r\n     '10'  =  SUM  (  CASE   WHEN   x  =  10   THEN   element   ELSE   0   END  ),  \r\n     'total'  =  COALESCE  (  SUM  (  element  ),  0  ) \r\n     FROM   (  SELECT   number   FROM   numbers   WHERE   number   <=  10  )   ydimension \r\n     LEFT   OUTER   JOIN    matrix   ON   ydimension.number   =  y   AND   name  =  'z' \r\n     GROUP BY   ydimension.number   WITH   ROLLUP  \r\n     ORDER BY   GROUPING  (  ydimension.number  ),  ydimension.number\r\n     <\/pre>\n
\"600-tenth.jpg\"<\/p>\n
If this is tedious, then use a procedure. Well, of course it’s tedious!<\/p>\n
     IF   OBJECT_ID   (  N'AggregateMatrix'  )   IS   NOT NULL \r\n        DROP PROCEDURE   AggregateMatrix \r\n    GO\r\n     CREATE PROCEDURE   AggregateMatrix \r\n    @Name  VARCHAR  (  5  )  =  'A'  ,   @Aggregation   VARCHAR  (  10  )  =  'SUM' \r\n     AS \r\n    DECLARE  @ii   INT  ,  @Max   INT  ,  @SQL   VARCHAR  (  8000  ) \r\n     SELECT   @ii  =  1  ,  @max  =  MAX  (  x  )   FROM   matrix   WHERE   [name]  =  @Name \r\n     SELECT   @SQL  =  'Select [X\/Y]=case when grouping(y)=0  \r\n    then cast(y as varchar(6)) \r\n    else '''n> +  @Aggregation  +  '''  \r\n      end,\r\n    '\r\n     WHILE   @ii  <=  @max \r\n        SELECT   @SQL  =  @SQL  +  ''''  +  CAST  (  @ii   AS VARCHAR  (  5  )) \r\n                       + '''='  +  @Aggregation  +  '(case when x='  +  CAST  (  @ii   AS VARCHAR  (  5  )) \r\n                       + ' then element else 0 end),'  ,  @ii  =  @ii  +  1 \r\n     SELECT   @SQL  =  @SQL  +  '''total''='  +  @Aggregation  +  '(element) \r\n    from matrix where [name]=''' +  @name  +  ''' \r\n    group by y with rollup order by grouping(y),y'\r\n     EXECUTE   (  @SQL  ) \r\n     IF   @@error  >  0   PRINT   @SQL \r\n    GO\r\n    <\/pre>\n
And while we’re about it, we’ll add a procedure to do a simple matrix. This will just be a cut-down version of the one above.<\/p>\n
     IF   OBJECT_ID   (  N'SimpleMatrix'  )   IS   NOT NULL \r\n        DROP PROCEDURE   SimpleMatrix \r\n    GO\r\n     CREATE PROCEDURE   SimpleMatrix \r\n    @Name  VARCHAR  (  5  )  =  'A' \r\n     AS \r\n    DECLARE  @ii   INT  ,  @Max   INT  ,  @SQL   VARCHAR  (  8000  ) \r\n     SELECT   @ii  =  1  ,  @max  =  MAX  (  x  )   FROM   matrix   WHERE   [name]  =  @Name \r\n     SELECT   @SQL  =  'Select [X\/Y]=cast(y as varchar(6))' \r\n     WHILE   @ii  <=  @max \r\n        SELECT   @SQL  =  @SQL  +  ',  \r\n    ''' +  CAST  (  @ii   AS VARCHAR  (  5  )) \r\n           + '''=sum(case when x='  +  CAST  (  @ii   AS VARCHAR  (  5  )) \r\n           + ' then element else 0 end)'  ,  @ii  =  @ii  +  1 \r\n     SELECT   @SQL  =  @SQL  +  ' \r\n    from matrix where [name]=''' +  @name  +  ''' group by y' \r\n    \r\n     EXECUTE   (  @SQL  ) \r\n     IF   @@error  >  0   PRINT   @SQL \r\n    GO \r\n    <\/pre>\n
Okay, let’s try them out!<\/p>\n
     EXECUTE   SimpleMatrix   'a'  \r\n     <\/pre>\n
\"600-second.jpg\"<\/p>\n
In order to do a few common matrix examples, we need to devise a few helper functions to allow us to get matrix data in there easily, in the format that is easy to check for errors. This is very handy when you are doing regression testing.<\/p>\n
Before we do the routine, let’s just make sure you have a number table…<\/p>\n
     IF   OBJECT_ID   (  N'spMaybeBuildNumberTable'  )   IS   NOT NULL \r\n        DROP PROCEDURE   spMaybeBuildNumberTable \r\n    GO\r\n     CREATE PROCEDURE   spMaybeBuildNumberTable  \r\n    @size  INT=  10000   --or whatever size you want to have for your number table \r\n     AS  \r\n    BEGIN \r\n    SET NOCOUNT ON \r\n    IF  NOT EXISTS (  SELECT   *   FROM   dbo.sysobjects   --check to make sure you have one \r\n       WHERE   id   =   OBJECT_ID  (  N'[dbo].[Numbers]'  )  \r\n       AND  OBJECTPROPERTY  (  id  ,   N'IsUserTable'  )   =   1  )  \r\n         BEGIN   --create it and stock it. \r\n         CREATE TABLE   [dbo].[Numbers]  (  \r\n          [number] [int]  ,  \r\n         CONSTRAINT   [Index_Numbers]   PRIMARY KEY CLUSTERED  \r\n         (  \r\n          [number]   ASC  \r\n         )   ON   [PRIMARY]  \r\n         )   ON   [PRIMARY]  \r\n        --right lets stock it. Jeff would hate this code. \r\n         DECLARE   @ii   INT  \r\n        SELECT  @ii  =  1  \r\n         WHILE   (  @ii  <=  @size  )  \r\n          BEGIN  \r\n         INSERT INTO  NUMBERS  (  NUMBER  )   SELECT   @II  \r\n          SELECT   @II  =  @II  +  1  \r\n          END  \r\n        END \r\n    END\r\n    <\/pre>\n
Now we can add the function that takes a string representation of a matrix, just like you see in the textbooks, and returns the SQL equivalent (please try it out just to see what it does). It could be done iteratively, but we have opted for the must faster TSQL-Special technique. If you are using a different dialect, you’ll have to do it the slow iterative way instead, but we’re using SQL Server!<\/p>\n
     GO  IF   OBJECT_ID   (  N'dbo.MatrixValuesOf'  )   IS   NOT NULL   \r\n     DROP FUNCTION   dbo.MatrixValuesOf \r\n    GO\r\n    \r\n     CREATE FUNCTION   dbo.MatrixValuesOf  ( \r\n     @Matrix   VARCHAR  (  MAX  ), \r\n     @Name   VARCHAR  (  10  ) \r\n    )\r\n     RETURNS   @MatrixValues   TABLE  \r\n     ( \r\n     -- columns returned by the function \r\n     name   VARCHAR  (  10  ) NOT NULL, \r\n     x   INT   NOT NULL, \r\n     y   INT   NOT NULL, \r\n     [Value] NUMERIC  (  8  ,  2  ) \r\n    )\r\n     AS \r\n    \r\n    BEGIN\r\n    DECLARE  @y   INT  ,   @x   INT  ,   @Value   VARCHAR  (  200  ) \r\n     DECLARE   @MyTable   TABLE   (  TheOrder   INT PRIMARY KEY  ,  \r\n     TheChar   CHAR  (  1  ) NOT NULL,  x   INT  ,  y   INT  , \r\n     Thevalue NUMERIC  (  8  ,  2  ), \r\n     strvalue   VARCHAR  (  200  ))  \r\n     SELECT   @x  =  1  ,   @y  =  1  ,  @Value  =  '' \r\n     INSERT INTO   @MyTable   (  TheOrder  ,  TheChar  )  \r\n     SELECT   number  ,   SUBSTRING  (  @Matrix  ,  number  ,  1  ) \r\n     FROM   numbers   WHERE   number  <=  LEN  (  @Matrix  ) \r\n    \r\n     UPDATE   @MyTable  \r\n     SET   @x   =  x  =   CASE   WHEN   TheChar  =  ','   THEN   @x  +  1   WHEN   TheChar  =CHAR  (  13  )   THEN   1   ELSE   @x   END  , \r\n     @y   =  y  =   CASE   WHEN   TheChar  =CHAR  (  13  )   THEN   @y  +  1   ELSE   @y   END  , \r\n     @value  =  strvalue  =  CASE   WHEN   TheChar   IN   (  ','  ,  CHAR  (  13  ))   THEN   ''  \r\n     WHEN   '1234567890-+.'   LIKE   '%'  +  theChar  +  '%'   THEN   @value  +  TheChar   ELSE   @value   END \r\n    INSERT INTO  @MatrixValues \r\n     SELECT   @Name  ,   x  ,   y  ,   CAST  (  RTRIM  (  LTRIM  (  CASE   WHEN   ISNUMERIC  (  strValue  )  =  1   THEN   strValue   ELSE   '0'   END  ))   AS   NUMERIC  (  8  ,  2  ))  \r\n     FROM   @MyTable   WHERE   TheOrder   IN   (  SELECT   MAX  (  TheOrder  )   FROM   @MyTable   GROUP BY   x  ,  y  ) \r\n     RETURN \r\n    END\r\n    GO\r\n    <\/pre>\n
So let’s test it out now….<\/p>\n
     DECLARE   @matrixA   VARCHAR  (  2000  ) \r\n     SELECT   @MatrixA  = \r\n     '2,  34,  67.78 \r\n     4,   5,   7\r\n     5, 457,   8.65'\r\n     SELECT   *   FROM   MatrixValuesOf  (  @MatrixA  ,  'a'  ) \r\n     <\/pre>\n
Wee!! that worked, didn’t it!<\/p>\n
     \/*\r\n    name       x           y           Value\r\n    ---------- ----------- ----------- --------\r\n    a          1           1           2.00\r\n    a          2           1           34.00\r\n    a          3           1           67.78\r\n    a          1           2           4.00\r\n    a          2           2           5.00\r\n    a          3           2           7.00\r\n    a          1           3           5.00\r\n    a          2           3           457.00\r\n    a          3           3           8.65\r\n    *\/\r\n    <\/pre>\n
Let’s double-check…<\/p>\n
     DELETE FROM   matrix   WHERE   [Name]  =  'Z' \r\n     INSERT INTO   matrix  \r\n     SELECT   *   FROM   MatrixValuesOf  ( \r\n     '5.31, 1.54, 0.09, 3.60, 8.64, 1.01, 8.05, 3.59, 2.61, 8.59 \r\n     4.61, 2.29, 9.16, 4.19, 8.17, 0.29, 6.92, 9.96, 9.00, 3.68\r\n     8.56, 9.67, 6.40, 4.25, 2.39, 7.30, 0.42, 2.62, 3.73, 2.54\r\n     8.86, 6.47, 1.06, 6.98, 3.54, 1.05, 2.93, 1.85, 4.35, 4.11\r\n     4.14, 1.64, 2.35, 2.11, 6.08, 6.52, 3.07, 9.62, 0.67, 8.32'\r\n     ,  'z'  ) \r\n     EXECUTE   SimpleMatrix   'z' \r\n     <\/pre>\n
\"600-third.jpg\"<\/p>\n
Now we can get started!<\/p>\n
Matrix multiplication by a scalar<\/h2>\n
     DELETE FROM   matrix   WHERE   [Name]  =  's' \r\n     INSERT INTO   matrix  \r\n        SELECT   *   FROM   MatrixValuesOf  ( \r\n     ' 4,   5, -56 \r\n     14,  11,  43\r\n    -43,  -4,  62'  , \r\n     's'  ) \r\n     INSERT INTO   matrix \r\n        SELECT   't'  ,  x  ,  y  ,  element  *  2 \r\n        FROM   matrix s \r\n        WHERE   s.[name]  =  's'  \r\n        <\/pre>\n
And let’s see the result….<\/p>\n
     EXECUTE   SimpleMatrix   't' \r\n     <\/pre>\n
So scalar arithmetic is absurdly simple and need concern us no longer.<\/p>\n
\"600-fourth.jpg\"<\/p>\n
Matrix Addition<\/h2>\n
Here is how we do Matrix Addition.<\/p>\n
    --pop in Matrix A\r\n     DELETE FROM   matrix   WHERE   [Name]  =  'a' \r\n     INSERT INTO   matrix  \r\n        SELECT   *   FROM   MatrixValuesOf  ( \r\n     '10, 13, -2 \r\n     12,  1,  4\r\n     -4, -4,  6'  , \r\n     'a'  ) \r\n    \r\n     --add in Matrix B \r\n     DELETE FROM   matrix   WHERE   [Name]  =  'b' \r\n     INSERT INTO   matrix  \r\n        SELECT   *   FROM   MatrixValuesOf  ( \r\n     ' 3, 24,  2 \r\n     -4, 14, 46\r\n      1,  3, -5'  , \r\n     'b'  ) \r\n\r\n     --now create a matrix 'c' which is the matrix addition of A and B \r\n     DELETE FROM   matrix   WHERE   [Name]  =  'c' \r\n     INSERT INTO   matrix \r\n        SELECT   'c'  ,  a.x  ,  a.y  ,  a.element  +  b.element \r\n        FROM   matrix a \r\n        INNER JOIN   matrix b \r\n        ON   a.x  =  b.x \r\n        AND   a.y  =  b.y \r\n        WHERE   a.[name]  =  'a'   AND   b.[name]  =  'b' \r\n<\/pre>\n
And let’s see the result….<\/p>\n
     EXECUTE   SimpleMatrix   'c' \r\n     \/* \r\n    X\/Y    1             2             3\r\n    ------ ------------- ------------- -------------\r\n    1      13.00         37.00          0.00\r\n    2       8.00         15.00         50.00\r\n    3      -3.00         -1.00          1.00\r\n    \r\n    *\/\r\n    <\/pre>\n
Matrix subtraction<\/h2>\n
And matrix subtraction is just as easy.<\/p>\n
     DELETE FROM   matrix   WHERE   [Name]  =  'd' \r\n     INSERT INTO   matrix \r\n        SELECT   'd'  ,  a.x  ,  a.y  ,  a.element  -  b.element \r\n        FROM   matrix a \r\n        INNER JOIN   matrix b \r\n        ON   a.x  =  b.x \r\n        AND   a.y  =  b.y \r\n        WHERE   a.[name]  =  'a'   AND   b.[name]  =  'b' \r\n        <\/pre>\n
And now let’s see it!<\/p>\n
     EXECUTE   SimpleMatrix   'd' \r\n     \/* \r\n    X\/Y    1             2             3\r\n    ------ ------------- ------------- -------------\r\n    1      7.00          -11.00         -4.00\r\n    2     16.00          -13.00        -42.00\r\n    3     -5.00           -7.00         11.00\r\n    \r\n    *\/\r\n    <\/pre>\n
Matrix Multiplication<\/h2>\n
So you think you can do the same thing with matrix multiplication eh? Just use a ‘*’ instead of a ‘-‘? Think again. this is where it gets more complicated.
\n We’ll multiply two nice simple matrices…<\/p>\n
     DELETE FROM   matrix   WHERE   name   IN   (  'e'  ,  'f'  ) \r\n     INSERT INTO   matrix  \r\n        SELECT   *   FROM   MatrixValuesOf  ( \r\n     ' 1, 2, 3 \r\n      4, 5, 6'  , \r\n     'e'  ) \r\n    \r\n     INSERT INTO   matrix  \r\n        SELECT   *   FROM   MatrixValuesOf  ( \r\n     ' 7,  8 \r\n      9, 10\r\n      11,12'  , \r\n     'f'  ) \r\n    \r\n     \/* create the result of the matrix multiplication *\/ \r\n     INSERT INTO   matrix  \r\n     SELECT   'g'  ,  xf  ,  ye  ,  SUM  (  ee  *  ef  ) \r\n        FROM \r\n        (  SELECT   [ee]  =  e.element  ,   [ef]  =  f.element  ,  [xe]  =  e.x  ,  [ye]  =  e.y  ,  [xf]  =  f.x  ,  [yf]  =  f.y \r\n        FROM   matrix e  \r\n        INNER JOIN \r\n           Matrix f \r\n        ON   e.x  =  f.y \r\n        WHERE   e.[name]  =  'e'   AND   f.[name]  =  'f'  )  pairs \r\n     GROUP BY   ye  ,  xf \r\n    \r\n     EXECUTE   SimpleMatrix   'g' \r\n     <\/pre>\n
\"600-fifth.jpg\"<\/p>\n
Matrix Horizontal concatenation<\/h2>\n
Horizontal concatenation is cool.<\/p>\n
    --pop in Matrix A\r\n     DELETE FROM   matrix   WHERE   [Name]  =  'a' \r\n     INSERT INTO   matrix  \r\n        SELECT   *   FROM   MatrixValuesOf  ( \r\n     '10, 13, -2 \r\n    12,  1,  4\r\n    -4, -4,  6'  , \r\n     'a'  ) \r\n\r\n     --add in Matrix B \r\n     DELETE FROM   matrix   WHERE   [Name]  =  'b' \r\n     INSERT INTO   matrix  \r\n        SELECT   *   FROM   MatrixValuesOf  ( \r\n     ' 3, 24,  2 \r\n    -4, 14, 46\r\n      1,  3, -5'  , \r\n     'b'  ) \r\n\r\n     --now create a matrix 'c' which is A and B concatenated ( normally, you'd put the MAX value into a variable, of course but we wanted to keep it simple) \r\n     DELETE FROM   matrix   WHERE   [Name]  =  'c' \r\n     INSERT INTO   matrix  \r\n        SELECT   'c'  ,   [x]  =  CASE   name   WHEN   'a'   THEN   x   ELSE   x  +(  SELECT   MAX  (  x  )   FROM   matrix   WHERE   [name]  =  'a'  )   END  ,  y  ,   element \r\n     FROM   matrix t   WHERE   t.[name]   IN   (  'a'  ,  'b'  )  \r\n    \r\n     EXECUTE   SimpleMatrix   'c' \r\n     <\/pre>\n
\"600-sixth.jpg\"<\/p>\n
Matrix Vertical Concatenation<\/h2>\n
Or we can do it vertically (sentiment about the variable applies here too).<\/p>\n
     DELETE FROM   matrix   WHERE   [Name]  =  'c' \r\n     INSERT INTO   matrix  \r\n     SELECT   'c'  ,   [x]  =  x  ,  [y]  =  CASE   name   WHEN   'a'   THEN   y   ELSE   y  +(  SELECT   MAX  (  y  )   FROM   matrix   WHERE   [name]  =  'a'  )   END  ,   element \r\n     FROM   matrix t   WHERE   t.[name]   IN   (  'a'  ,  'b'  )  \r\n\r\n     EXECUTE   SimpleMatrix   'c' \r\n    <\/pre>\n
\"600-ninth.jpg\"<\/p>\n
Matrix Transposition<\/h2>\n
Fine, so how do we do Matrix transposition?<\/p>\n
     DELETE FROM   matrix   WHERE   [Name]  =  't' \r\n     INSERT INTO   matrix  \r\n        SELECT   *   FROM   MatrixValuesOf  ( \r\n     '1,  2,  3 \r\n    4,  5,  6'  , \r\n     't'  ) \r\n     DELETE FROM   matrix   WHERE   [Name]  =  'u' \r\n     INSERT INTO   matrix  \r\n     SELECT   'u'  ,   [x]  =  y  ,   [y]  =  x  ,   element \r\n     FROM   matrix t   WHERE   t.[name]  =  't'  \r\n    \r\n     EXECUTE   SimpleMatrix   'u'  --and display the transposed matrix \r\n     <\/pre>\n
\"600-seventh.jpg\"<\/p>\n
Rendering a matrix as a string<\/h2>\n
So now, just to finish off with a flourish, we’ll reverse-out our MatrixValuesOf<\/code> Function, so we can represent the results in a string. This means that we could make an easier test harness and could squirrel matrixes away in string form.<\/p>\n
     IF   OBJECT_ID   (  N'StringMatrix'  )   IS   NOT NULL \r\n        DROP PROCEDURE   StringMatrix \r\n    GO\r\n     CREATE PROCEDURE   StringMatrix \r\n    @Name  VARCHAR  (  5  )  =  'A'  , \r\n     @String   VARCHAR  (  MAX  )   OUTPUT \r\n     AS \r\n    SET NOCOUNT ON\r\n    DECLARE  @ii   INT  ,  @Max   INT  ,  @SQL   NVARCHAR  (  4000  ) \r\n     SELECT   @ii  =  1  ,  @max  =  MAX  (  x  )   FROM   matrix   WHERE   [name]  =  @Name \r\n     WHILE   @ii  <=  @max \r\n        SELECT   @SQL  =  COALESCE  (  @SQL  +  ' \r\n                  +' ,  'Select @string=coalesce(@String,'''')+'  )+  'right(''          ''+cast(cast(sum(case when x='  +  CAST  (  @ii   AS VARCHAR  (  5  )) \r\n           + ' then element else 0 end) as numeric(9,2)) as varchar(12)),10)'  +  CASE   WHEN   @ii  <  @max   THEN   '+'','''   ELSE   '+'' \r\n    '''  END  ,  @ii  =  @ii  +  1 \r\n     SELECT   @SQL  =  @SQL  +  ' \r\n    from matrix where [name]=''' +  @name  +  ''' group by y' \r\n     EXECUTE   sp_ExecuteSQL   @SQL  ,  N' @String varchar(max) output'  ,   @String  =  @String OUTPUT \r\n     IF   @@error  >  0   PRINT   @SQL \r\n    GO \r\n    \r\n     DECLARE   @String   VARCHAR  (  MAX  ) \r\n     EXECUTE   StringMatrix   'a'  ,  @String OUTPUT \r\n     SELECT   @String \r\n    \r\n     \/*   10.00,     13.00,     -2.00 \r\n         12.00,      1.00,      4.00\r\n         -4.00,     -4.00,      6.00 \r\n    \r\n    So we are ready for a more exacting test... *\/\r\n     DELETE FROM   matrix   WHERE   [Name]  =  'x' \r\n     INSERT INTO   matrix  \r\n        SELECT   *   FROM   MatrixValuesOf  ( \r\n     '   192.96,    -55.82,     92.33,     55.86,     58.47,     11.05,     53.26 \r\n        192.08,    177.95,    167.00,    196.71,     79.70,     91.51,     58.13\r\n         12.00,     54.32,    104.72,     63.03,    183.26,    169.45,    117.58\r\n        102.87,    189.95,     19.50,    170.58,      0.01,     80.48,    146.79\r\n        171.93,     62.35,     88.21,     54.73,     91.39,    151.02,    175.50\r\n         44.19,    150.64,    196.40,     17.24,    133.31,    102.52,    146.05\r\n        199.20,    190.81,    -38.56,     53.04,     47.94,    178.62,     48.57\r\n        104.10,     41.95,    175.17,   -100.81,     28.55,     82.87,     38.74\r\n        126.57,     26.02,     20.88,     59.68,     82.01,     92.68,    119.01\r\n        184.44,     27.17,     85.33,    139.79,    123.22,    112.38,     38.28\r\n        195.55,      3.82,    170.43,    170.51,    149.36,    118.25,     78.22\r\n        135.32,     36.93,     28.97,   -111.45,    168.44,     68.15,    189.48\r\n         84.04,    107.10,     19.77,      3.20,     69.30,    175.64,     29.29\r\n         16.90,     42.28,     73.41,    154.76,     81.55,     57.64,     96.12\r\n         66.61,     37.63,     62.82,     87.71,    102.84,    110.43,    185.25'\r\n     ,  'x'  ) \r\n     GO \r\n     DECLARE   @String   VARCHAR  (  MAX  ) \r\n     EXECUTE   StringMatrix   'x'  ,  @String OUTPUT \r\n     SELECT   @String \r\n     \/* which produces this! (phew) \r\n    \r\n        192.96,    -55.82,     92.33,     55.86,     58.47,     11.05,     53.26\r\n        192.08,    177.95,    167.00,    196.71,     79.70,     91.51,     58.13\r\n         12.00,     54.32,    104.72,     63.03,    183.26,    169.45,    117.58\r\n        102.87,    189.95,     19.50,    170.58,      0.01,     80.48,    146.79\r\n        171.93,     62.35,     88.21,     54.73,     91.39,    151.02,    175.50\r\n         44.19,    150.64,    196.40,     17.24,    133.31,    102.52,    146.05\r\n        199.20,    190.81,    -38.56,     53.04,     47.94,    178.62,     48.57\r\n        104.10,     41.95,    175.17,   -100.81,     28.55,     82.87,     38.74\r\n        126.57,     26.02,     20.88,     59.68,     82.01,     92.68,    119.01\r\n        184.44,     27.17,     85.33,    139.79,    123.22,    112.38,     38.28\r\n        195.55,      3.82,    170.43,    170.51,    149.36,    118.25,     78.22\r\n        135.32,     36.93,     28.97,   -111.45,    168.44,     68.15,    189.48\r\n         84.04,    107.10,     19.77,      3.20,     69.30,    175.64,     29.29\r\n         16.90,     42.28,     73.41,    154.76,     81.55,     57.64,     96.12\r\n         66.61,     37.63,     62.82,     87.71,    102.84,    110.43,    185.25\r\n    <\/pre>\n
So, what goes in comes out (it took a little bit of heaving and grunting). So this is as far as we’ll take it in this workbench and we’ll leave you to figure out a few little puzzles such as inversion and determinants. Now, the next puzzle is how one can use these techniques to solve other programming puzzles. It would be fascinating to learn how this sort of technique can be used.<\/p>\n","protected":false},"excerpt":{"rendered":"
In this workbench, Robyn Page and Phil Factor decide to tackle the subject of Matrix handling and Matrix Mathematics in SQL. They maintain that ‘One just needs a clear head and think in terms of set-based operations’…<\/p>\n","protected":false},"author":221812,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4150,4151,4252,4460],"coauthors":[6813,6814],"class_list":["post-470","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-sql","tag-sql-server","tag-t-sql-programming","tag-workbench"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/470","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221812"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=470"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/470\/revisions"}],"predecessor-version":[{"id":74374,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/470\/revisions\/74374"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=470"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=470"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=470"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=470"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}