NMATRIX2STRING_q
Updated: 31 January 2012
Use the scalar function NMATRIX2STRING_q 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
SELECT [wctMath].[wct].[NMATRIX2STRING_q](
<@Matrix_RangeQuery, nvarchar(max),>)
Arguments
@Matrix_RangeQuery
The SELECT statement, as a string, which, when executed, creates the resultant table of x- and y-values which will be used in the calculation. @Matrix_RangeQuery 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.
· For simpler queries in SQL Server 2005, consider using the scalar function NMATRIX2STRING.
· For more complex queries and better performance in SQL Server 2008 and SQL Server 2012, consider using the aggregate function NMATRIX2STRING.
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)
SELECT wct.NMATRIX2STRING_q('SELECT r,c,x FROM #m') as A
We can enter the following SQL to turn this data into a formatted string representation of the matrix.
SELECT wct.NMATRIX2STRING_q('SELECT r,c,x FROM #m') as A
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_q('SELECT r,c,x FROM #m WHERE r > 2 and c > 2') as A
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_q('SELECT r,1,x FROM #m WHERE r = c') as A
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_q('SELECT r,c,x FROM #m WHERE id = ' + CHAR(39) + 'A3' + CHAR(39)) as A
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 n.id
,wct.NMATRIX2STRING_q('SELECT r,c,x FROM #m WHERE id = ' + CHAR(39) + n.id + CHAR(39)) as A
FROM (SELECT DISTINCT id from #m) n(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
|