MATRIX2STRING
Updated: 31 January 2012
Use the scalar function MATRIX2STRING 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](
<@Matrix_TableName, nvarchar(max),>
,<@ColumnNames, nvarchar(4000),>
,<@GroupedColumnName, nvarchar(4000),>
,<@GroupedColumnValue, sql_variant,>)
Arguments
@Matrix_TableName
the name, as text, of the table or view that contains the values in the array to be used in the MATRIX2STRING formatting.
@ColumnNames
the name, as text, of the columns in the table or view specified by @TableName that contains the array values to be used in the MATRIX2STRING formatting. Data returned from the @ColumnNames must be of the type float or of a type that implicitly converts to float.
@GroupedColumnName
the name, as text, of the column in the table or view specified by @TableName which will be used for grouping the results.
@GroupedColumnValue
the column value to do the grouping on.
Return Types
[nvarchar](max)
Remarks
· To convert normalized data to a string format, use the NMatrix2String or the NMatrix2String_q function.
· For more complex queries, consider using the MATRIX2STRING_q function.
· For larger datasets, it is better to use the MATRIX2STRING_q function so that you can specify an ordering column(s). There is no guarantee that in a large dataset, MATRIX2STRING will put the matrix in row order.
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('#m','*','',NULL) 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 a value for @GroupedColumnName, as there was no grouping required. This is also why the @GroupedColumnValue is NULL.
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('#m','x0,x1,x2,x3,x4,x5','m','L3') 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('#m','x0,x1,x2,x3,x4,x5','m',n.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
|