Login     Register

        Contact Us     Search

XLeratorDB/math Documentation

SQL Server matrix-to-string function


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

 


Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service