MATRIX2STRING_q
Updated: 31 January 2012
Use the scalar function MATRIX2STRING_q to turn 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].[MATRIX2STRING_q](
<@Matrix_RangeQuery, nvarchar(max),>)
Arguments
@MatrixRangeQuery
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. @MatrixRangeQuery values must evaluate to a type of float or of a type that implicitly converts to float.
Return Types
[nvarchar](max)
Remarks
· To convert normalized data to a string format, use the NMatrix2String or the NMatrix2String_q function.
· For simpler queries, consider using the MATRIX2STRING function.
· For larger datasets, it is best to specify an ORDER BY clause to specify an ordering column(s).
Examples
The following query populates a temp table, ‘#m’, with a 10-by-6 matrix of values.
SELECT *
INTO #m
FROM (
SELECT -79,-45, 9, 9,-91, -5 UNION ALL
SELECT 68, 46, 9, 81,-61, 35 UNION ALL
SELECT 83,-25, 80,-67,-22,-38 UNION ALL
SELECT 77, 40,-24, 69, 73,-20 UNION ALL
SELECT -17,-72, -9,-72, -6,-34 UNION ALL
SELECT 64,-47, 48,-54, 18, 11 UNION ALL
SELECT -4,-36, 7,-56,-34, -3 UNION ALL
SELECT -41, 90, 78,-43, 38, 64 UNION ALL
SELECT -60,-85,-31,-83,-96,-36 UNION ALL
SELECT -40, 31,-93,-62, 64, 10
) n(x0,x1,x2,x3,x4,x5)
We can enter the following SQL to turn this data into a formatted string representation of the matrix.
SELECT wct.MATRIX2STRING_q('SELECT * FROM #m') as MATRIX
This produces the following result.
MATRIX
-----------------------------------------------------------------------------
-79,-45,9,9,-91,-5;68,46,9,81,-61,35;83,-25,80,-67,-22,-38;77,40,-24,69,73,-20;-17,-72,-9,-72,-6,-34;64,-47,48,-54,18,11;-4,-36,7,-56,-34,-3;-41,90,78,-43,38,64;-60,-85,-31,-83,-96,-36;-40,31,-93,-62,64,10
Since the only rows in the table were for the matrix that we want to format, there was no need to specify an ORDER BY clause.
In this example, we will expand the number of columns in the matrix to include a matrix and a row number.
SELECT *
INTO #m
FROM (
SELECT 'L1',0,-79,-45, 9, 9,-91, -5 UNION ALL
SELECT 'L1',1, 68, 46, 9, 81,-61, 35 UNION ALL
SELECT 'L1',2, 83,-25, 80,-67,-22,-38 UNION ALL
SELECT 'L1',3, 77, 40,-24, 69, 73,-20 UNION ALL
SELECT 'L1',4,-17,-72, -9,-72, -6,-34 UNION ALL
SELECT 'L1',5, 64,-47, 48,-54, 18, 11 UNION ALL
SELECT 'L1',6, -4,-36, 7,-56,-34, -3 UNION ALL
SELECT 'L1',7,-41, 90, 78,-43, 38, 64 UNION ALL
SELECT 'L1',8,-60,-85,-31,-83,-96,-36 UNION ALL
SELECT 'L1',9,-40, 31,-93,-62, 64, 10 UNION ALL
SELECT 'L2',0, -6, 44,-37, 36, 16,-34 UNION ALL
SELECT 'L2',1,-28, 11,-95, 29,-70,-37 UNION ALL
SELECT 'L2',2, 58, 89,-85,-31, 85,-84 UNION ALL
SELECT 'L2',3, 86,-85,-48,-74, -5,-32 UNION ALL
SELECT 'L2',4, 7, 56,-72,-76,-42,-85 UNION ALL
SELECT 'L2',5,-84,-60, 48,-15,-95, 9 UNION ALL
SELECT 'L2',6, 72,-22,-56,-45, 76,-83 UNION ALL
SELECT 'L2',7, 21, 66, 60, 67,-38,-99 UNION ALL
SELECT 'L2',8, 60, 54, 56,-79, 35,-65 UNION ALL
SELECT 'L2',9, 1,-58, 43,-95, 68,-89 UNION ALL
SELECT 'L3',0,-70, 73, 0, 23, 22, 72 UNION ALL
SELECT 'L3',1,-80, -6,-66,-17,-30, 50 UNION ALL
SELECT 'L3',2,-88, 69, 78,-60, 32,-56 UNION ALL
SELECT 'L3',3, 52,-18, 21,-54,-27,-82 UNION ALL
SELECT 'L3',4, 97, 63, 5, 13, 5, 34 UNION ALL
SELECT 'L3',5, 20,100,-71, 42,-67, 32 UNION ALL
SELECT 'L3',6,-39, 34, 29, 47,-59, 6 UNION ALL
SELECT 'L3',7, 17, 24,-32, 0,-11, 58 UNION ALL
SELECT 'L3',8,-75,-19, 49,-66, 2, 15 UNION ALL
SELECT 'L3',9, 44,-74, 79, 1,-56, 76
) n(m,r,x0,x1,x2,x3,x4,x5)
Here we will format a string for the Matrix L3.
SELECT wct.MATRIX2STRING_q(
'SELECT x0,x1,x2,x3,x4,x5
FROM #m
WHERE m = ' + CHAR(39) + 'L3' + CHAR(39) + ' ORDER BY m'
) as MATRIX
This produces the following result.
MATRIX
-----------------------------------------------------------------------------
-70,73,0,23,22,72;-80,-6,-66,-17,-30,50;-88,69,78,-60,32,-56;52,-18,21,-54,-27,-82;97,63,5,13,5,34;20,100,-71,42,-67,32;-39,34,29,47,-59,6;17,24,-32,0,-11,58;-75,-19,49,-66,2,15;44,-74,79,1,-56,76
In this select, we will format all the matrices and group them together by matrix name.
SELECT n.m
,wct.MATRIX2STRING_q(
'SELECT x0,x1,x2,x3,x4,x5
FROM #m
WHERE m = ' + CHAR(39) + n.m + CHAR(39) + ' ORDER BY m'
) as MATRIX
FROM (SELECT DISTINCT m from #m) n(m)
This produces the following result.
m MATRIX
---- ------------------------------------------------------------------------
|
L1
|
-79,-45,9,9,-91,-5;68,46,9,81,-61,35;83,-25,80,-67,-22,-38;77,40,-24,69,73,-20;-17,-72,-9,-72,-6,-34;64,-47,48,-54,18,11;-4,-36,7,-56,-34,-3;-41,90,78,-43,38,64;-60,-85,-31,-83,-96,-36;-40,31,-93,-62,64,10
|
|
L2
|
-6,44,-37,36,16,-34;-28,11,-95,29,-70,-37;58,89,-85,-31,85,-84;86,-85,-48,-74,-5,-32;7,56,-72,-76,-42,-85;-84,-60,48,-15,-95,9;72,-22,-56,-45,76,-83;21,66,60,67,-38,-99;60,54,56,-79,35,-65;1,-58,43,-95,68,-89
|
|
L3
|
-70,73,0,23,22,72;-80,-6,-66,-17,-30,50;-88,69,78,-60,32,-56;52,-18,21,-54,-27,-82;97,63,5,13,5,34;20,100,-71,42,-67,32;-39,34,29,47,-59,6;17,24,-32,0,-11,58;-75,-19,49,-66,2,15;44,-74,79,1,-56,76
|