MATRIX2STRING_q
Updated 2023-10-19 13:59:27.843000
Syntax
SELECT [westclintech].[wct].[MATRIX2STRING_q](
<@Matrix_RangeQuery, nvarchar(max),>)
Description
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.
Arguments
@Matrix_RangeQuery
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 Type
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.
{"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,-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.
{"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,-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.
{"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"}]}