Login     Register

        Contact Us     Search

XLeratorDB/math Documentation

SQL Server matrix-to-string function


NMATRIX2STRING

Updated: 31 January 2012


Note: This documentation is for the SQL2008 (and later) version of this XLeratorDB function, it is not compatible with SQL Server 2005.
Click here for the SQL2005 version of the NMATRIX2STRING function


Use the multi-input aggregate function NMATRIX2STRING to turn third-normal form table data into a string representation of a matrix, where the columns are separated by commas and the rows are separated by semi-colons. 
Syntax

Arguments
@Row
The name of the column which identifies the row of the matrix. @Row values must evaluate to a type of int or of a type that implicitly converts to int.
@Col
The name of the column which identifies the column of the matrix. @Col values must evaluate to a type of int or of a type that implicitly converts to int.
@Val
The name of the column which identifies the value at the row-column location of the matrix. @Val values must evaluate to a type of float or of a type that implicitly converts to float.
Return Types
[nvarchar](max)
Remarks
·         To convert de-normalized data to a string format, us the Matrix2String or the Matrix2String_q function.
Examples
The following query populates a temp table, ‘#m’, with a 5-by-5 matrix of values.
SELECT *
INTO #m
FROM (
      SELECT 1,1,-66 UNION ALL
      SELECT 1,2,46 UNION ALL
      SELECT 1,3,-54 UNION ALL
      SELECT 1,4,77 UNION ALL
      SELECT 1,5,-54 UNION ALL
      SELECT 2,1,-88 UNION ALL
      SELECT 2,2,-87 UNION ALL
      SELECT 2,3,-49 UNION ALL
      SELECT 2,4,53 UNION ALL
      SELECT 2,5,47 UNION ALL
      SELECT 3,1,32 UNION ALL
      SELECT 3,2,19 UNION ALL
      SELECT 3,3,2 UNION ALL
      SELECT 3,4,-30 UNION ALL
      SELECT 3,5,62 UNION ALL
      SELECT 4,1,-12 UNION ALL
      SELECT 4,2,66 UNION ALL
      SELECT 4,3,-49 UNION ALL
      SELECT 4,4,-30 UNION ALL
      SELECT 4,5,44 UNION ALL
      SELECT 5,1,-66 UNION ALL
      SELECT 5,2,-57 UNION ALL
      SELECT 5,3,96 UNION ALL
      SELECT 5,4,-84 UNION ALL
      SELECT 5,5,-10
      ) m(r,c,x)
 
We can enter the following SQL to turn this data into a formatted string representation of the matrix.
SELECT wct.NMATRIX2STRING(r,c,x) as A
FROM #m
This produces the following result.                                                        
A
-----------------------------------------------------------------------------
-66,46,-54,77,-54;-88,-87,-49,53,47;32,19,2,-30,62;-12,66,-49,-30,44;-66,-57,96,-84,-10
In this example, we will only take the 3-by-3 matrix in the lower right corner.
SELECT wct.NMATRIX2STRING(r,c,x) as A
FROM #m
WHERE r > 2
AND c > 2
This produces the following result.
A
------------------------------
2,-30,62;-49,-30,44;96,-84,-10
In this example we will take the diagonal of the matrix (without using the DIAG function).
SELECT wct.NMATRIX2STRING(r,1,x) as A
FROM #m
WHERE r = c
This produces the following result.
A
-----------------
-66;-87;2;-30;-10
In this example, we will add a matrix identifier to our table and then select the Matrix A3.
SELECT *
INTO #m
FROM (
      SELECT 'A1',1,1,-18 UNION ALL
      SELECT 'A1',1,2,98 UNION ALL
      SELECT 'A1',1,3,16 UNION ALL
      SELECT 'A1',1,4,-68 UNION ALL
      SELECT 'A1',1,5,13 UNION ALL
      SELECT 'A1',2,1,-57 UNION ALL
      SELECT 'A1',2,2,-69 UNION ALL
      SELECT 'A1',2,3,38 UNION ALL
      SELECT 'A1',2,4,48 UNION ALL
      SELECT 'A1',2,5,50 UNION ALL
      SELECT 'A1',3,1,1 UNION ALL
      SELECT 'A1',3,2,13 UNION ALL
      SELECT 'A1',3,3,71 UNION ALL
      SELECT 'A1',3,4,-21 UNION ALL
      SELECT 'A1',3,5,-82 UNION ALL
      SELECT 'A1',4,1,-87 UNION ALL
      SELECT 'A1',4,2,57 UNION ALL
      SELECT 'A1',4,3,-84 UNION ALL
      SELECT 'A1',4,4,-23 UNION ALL
      SELECT 'A1',4,5,-19 UNION ALL
      SELECT 'A1',5,1,-11 UNION ALL
      SELECT 'A1',5,2,-98 UNION ALL
      SELECT 'A1',5,3,-68 UNION ALL
      SELECT 'A1',5,4,38 UNION ALL
      SELECT 'A1',5,5,38 UNION ALL
      SELECT 'A2',1,1,55 UNION ALL
      SELECT 'A2',1,2,-84 UNION ALL
      SELECT 'A2',1,3,94 UNION ALL
      SELECT 'A2',1,4,32 UNION ALL
      SELECT 'A2',1,5,80 UNION ALL
      SELECT 'A2',2,1,63 UNION ALL
      SELECT 'A2',2,2,26 UNION ALL
      SELECT 'A2',2,3,-12 UNION ALL
      SELECT 'A2',2,4,34 UNION ALL
      SELECT 'A2',2,5,73 UNION ALL
      SELECT 'A2',3,1,-42 UNION ALL
      SELECT 'A2',3,2,13 UNION ALL
      SELECT 'A2',3,3,66 UNION ALL
      SELECT 'A2',3,4,-3 UNION ALL
      SELECT 'A2',3,5,87 UNION ALL
      SELECT 'A2',4,1,-28 UNION ALL
      SELECT 'A2',4,2,-30 UNION ALL
      SELECT 'A2',4,3,-52 UNION ALL
      SELECT 'A2',4,4,-23 UNION ALL
      SELECT 'A2',4,5,-52 UNION ALL
      SELECT 'A2',5,1,-52 UNION ALL
      SELECT 'A2',5,2,-78 UNION ALL
      SELECT 'A2',5,3,-47 UNION ALL
      SELECT 'A2',5,4,-80 UNION ALL
      SELECT 'A2',5,5,13 UNION ALL
      SELECT 'A3',1,1,-67 UNION ALL
      SELECT 'A3',1,2,-15 UNION ALL
      SELECT 'A3',1,3,-67 UNION ALL
      SELECT 'A3',1,4,1 UNION ALL
      SELECT 'A3',1,5,26 UNION ALL
      SELECT 'A3',2,1,-89 UNION ALL
      SELECT 'A3',2,2,80 UNION ALL
      SELECT 'A3',2,3,-8 UNION ALL
      SELECT 'A3',2,4,38 UNION ALL
      SELECT 'A3',2,5,100 UNION ALL
      SELECT 'A3',3,1,71 UNION ALL
      SELECT 'A3',3,2,0 UNION ALL
      SELECT 'A3',3,3,12 UNION ALL
      SELECT 'A3',3,4,-11 UNION ALL
      SELECT 'A3',3,5,-97 UNION ALL
      SELECT 'A3',4,1,79 UNION ALL
      SELECT 'A3',4,2,39 UNION ALL
      SELECT 'A3',4,3,81 UNION ALL
      SELECT 'A3',4,4,-39 UNION ALL
      SELECT 'A3',4,5,9 UNION ALL
      SELECT 'A3',5,1,-72 UNION ALL
      SELECT 'A3',5,2,91 UNION ALL
      SELECT 'A3',5,3,-84 UNION ALL
      SELECT 'A3',5,4,68 UNION ALL
      SELECT 'A3',5,5,-70
      ) m(id,r,c,x)
     
SELECT wct.NMATRIX2STRING(r,c,x) as A
FROM #m
WHERE id = 'A3'
This produces the following result.
A
-----------------------------------------------------------------------------
-67,-15,-67,1,26;-89,80,-8,38,100;71,0,12,-11,-97;79,39,81,-39,9;-72,91,-84,68,-70
 
In this example, we will produce three strings, one for each of the matrix identifiers.
SELECT id
,wct.NMATRIX2STRING(r,c,x) as A
FROM #m
GROUP BY id
This produces the following result.
id   A
---- ----------------------------------------------------------------------

A1
-18,98,16,-68,13;-57,-69,38,48,50;1,13,71,-21,-82;-87,57,-84,-23,-19;-11,-98,-68,38,38
A2
55,-84,94,32,80;63,26,-12,34,73;-42,13,66,-3,87;-28,-30,-52,-23,-52;-52,-78,-47,-80,13
A3
-67,-15,-67,1,26;-89,80,-8,38,100;71,0,12,-11,-97;79,39,81,-39,9;-72,91,-84,68,-70

 


Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service