MATRIX2STRING
Updated 2023-10-19 13:54:51.110000
Syntax
SELECT [westclintech].[wct].[MATRIX2STRING](
<@Matrix_TableName, nvarchar(max),>
,<@ColumnNames, nvarchar(4000),>
,<@GroupedColumnName, nvarchar(4000),>
,<@GroupedColumnValue, sql_variant,>)
Description
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.
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.
@GroupedColumnName
the name, as text, of the column in the table or view specified by @TableName which will be used for grouping the results.
@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.
@GroupedColumnValue
the column value to do the grouping on.
Return Type
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.
{"columns":[{"field":"MATRIX"}],"rows":[{"MATRIX":"-79,-45,9,9,-91,-5;68,46,9,81,-61,35;83,-25,80,-67,-22,-38;77,40,-24,69,73,-2"}]}
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.
{"columns":[{"field":"MATRIX"}],"rows":[{"MATRIX":"-70,73,0,23,22,72;-80,-6,-66,-17,-30,50;-88,69,78,-60,32,-56;52,-18,21,-54,-2"}]}
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.
{"columns":[{"field":"m"},{"field":"MATRIX"}],"rows":[{"m":"L1","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"},{"m":"L2","MATRIX":"-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"},{"m":"L3","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"}]}