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
|