NMATRIX2STRING
Updated: 31 January 2012
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
|