NMATRIX2STRING_q
Updated 2023-10-19 15:54:19.273000
Syntax
SELECT [westclintech].[wct].[NMATRIX2STRING_q](
<@Matrix_RangeQuery, nvarchar(max),>)
Description
Use the scalar function NMATRIX2STRING_q to turn third-normal form table data into a string representation of a matrix using dynamic SQL, 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. @Matrix_RangeQuery values must evaluate to a type of float or of a type that implicitly converts to float.
Return Type
nvarchar(max)
Remarks
To convert de-normalized data to a string format, us the Matrix2String or the Matrix2String_q function.
Examples
The following query populates a temp table, #m, with a 5-by-5 matrix of values.
SELECT *
INTO #m
FROM
(
SELECT 1,
1,
-66
UNION ALL
SELECT 1,
2,
46
UNION ALL
SELECT 1,
3,
-54
UNION ALL
SELECT 1,
4,
77
UNION ALL
SELECT 1,
5,
-54
UNION ALL
SELECT 2,
1,
-88
UNION ALL
SELECT 2,
2,
-87
UNION ALL
SELECT 2,
3,
-49
UNION ALL
SELECT 2,
4,
53
UNION ALL
SELECT 2,
5,
47
UNION ALL
SELECT 3,
1,
32
UNION ALL
SELECT 3,
2,
19
UNION ALL
SELECT 3,
3,
2
UNION ALL
SELECT 3,
4,
-30
UNION ALL
SELECT 3,
5,
62
UNION ALL
SELECT 4,
1,
-12
UNION ALL
SELECT 4,
2,
66
UNION ALL
SELECT 4,
3,
-49
UNION ALL
SELECT 4,
4,
-30
UNION ALL
SELECT 4,
5,
44
UNION ALL
SELECT 5,
1,
-66
UNION ALL
SELECT 5,
2,
-57
UNION ALL
SELECT 5,
3,
96
UNION ALL
SELECT 5,
4,
-84
UNION ALL
SELECT 5,
5,
-10
) m(r, c, x);
We can enter the following SQL to turn this data into a formatted string representation of the matrix.
SELECT wct.NMATRIX2STRING_q('SELECT r,c,x FROM #m') as A;
This produces the following result.
{"columns":[{"field":"A"}],"rows":[{"A":"-66,46,-54,77,-54;-88,-87,-49,53,47;32,19,2,-30,62;-12,66,-49,-30,44;-66,-57,96,-84,-10"}]}
In this example, we will only take the 3-by-3 matrix in the lower right corner.
SELECT wct.NMATRIX2STRING_q('SELECT r,c,x FROM #m WHERE r > 2 and c > 2') as A;
This produces the following result.
{"columns":[{"field":"A"}],"rows":[{"A":"2,-30,62;-49,-30,44;96,-84,-10"}]}
In this example we will take the diagonal of the matrix (without using the DIAG function).
SELECT wct.NMATRIX2STRING_q('SELECT r,1,x FROM #m WHERE r = c') as A;
This produces the following result.
{"columns":[{"field":"A"}],"rows":[{"A":"-66;-87;2;-30;-10"}]}
In this example, we will add a matrix identifier to our table and then select the Matrix A3.
SELECT *
INTO #m
FROM
(
SELECT 'A1',
1,
1,
-18
UNION ALL
SELECT 'A1',
1,
2,
98
UNION ALL
SELECT 'A1',
1,
3,
16
UNION ALL
SELECT 'A1',
1,
4,
-68
UNION ALL
SELECT 'A1',
1,
5,
13
UNION ALL
SELECT 'A1',
2,
1,
-57
UNION ALL
SELECT 'A1',
2,
2,
-69
UNION ALL
SELECT 'A1',
2,
3,
38
UNION ALL
SELECT 'A1',
2,
4,
48
UNION ALL
SELECT 'A1',
2,
5,
50
UNION ALL
SELECT 'A1',
3,
1,
1
UNION ALL
SELECT 'A1',
3,
2,
13
UNION ALL
SELECT 'A1',
3,
3,
71
UNION ALL
SELECT 'A1',
3,
4,
-21
UNION ALL
SELECT 'A1',
3,
5,
-82
UNION ALL
SELECT 'A1',
4,
1,
-87
UNION ALL
SELECT 'A1',
4,
2,
57
UNION ALL
SELECT 'A1',
4,
3,
-84
UNION ALL
SELECT 'A1',
4,
4,
-23
UNION ALL
SELECT 'A1',
4,
5,
-19
UNION ALL
SELECT 'A1',
5,
1,
-11
UNION ALL
SELECT 'A1',
5,
2,
-98
UNION ALL
SELECT 'A1',
5,
3,
-68
UNION ALL
SELECT 'A1',
5,
4,
38
UNION ALL
SELECT 'A1',
5,
5,
38
UNION ALL
SELECT 'A2',
1,
1,
55
UNION ALL
SELECT 'A2',
1,
2,
-84
UNION ALL
SELECT 'A2',
1,
3,
94
UNION ALL
SELECT 'A2',
1,
4,
32
UNION ALL
SELECT 'A2',
1,
5,
80
UNION ALL
SELECT 'A2',
2,
1,
63
UNION ALL
SELECT 'A2',
2,
2,
26
UNION ALL
SELECT 'A2',
2,
3,
-12
UNION ALL
SELECT 'A2',
2,
4,
34
UNION ALL
SELECT 'A2',
2,
5,
73
UNION ALL
SELECT 'A2',
3,
1,
-42
UNION ALL
SELECT 'A2',
3,
2,
13
UNION ALL
SELECT 'A2',
3,
3,
66
UNION ALL
SELECT 'A2',
3,
4,
-3
UNION ALL
SELECT 'A2',
3,
5,
87
UNION ALL
SELECT 'A2',
4,
1,
-28
UNION ALL
SELECT 'A2',
4,
2,
-30
UNION ALL
SELECT 'A2',
4,
3,
-52
UNION ALL
SELECT 'A2',
4,
4,
-23
UNION ALL
SELECT 'A2',
4,
5,
-52
UNION ALL
SELECT 'A2',
5,
1,
-52
UNION ALL
SELECT 'A2',
5,
2,
-78
UNION ALL
SELECT 'A2',
5,
3,
-47
UNION ALL
SELECT 'A2',
5,
4,
-80
UNION ALL
SELECT 'A2',
5,
5,
13
UNION ALL
SELECT 'A3',
1,
1,
-67
UNION ALL
SELECT 'A3',
1,
2,
-15
UNION ALL
SELECT 'A3',
1,
3,
-67
UNION ALL
SELECT 'A3',
1,
4,
1
UNION ALL
SELECT 'A3',
1,
5,
26
UNION ALL
SELECT 'A3',
2,
1,
-89
UNION ALL
SELECT 'A3',
2,
2,
80
UNION ALL
SELECT 'A3',
2,
3,
-8
UNION ALL
SELECT 'A3',
2,
4,
38
UNION ALL
SELECT 'A3',
2,
5,
100
UNION ALL
SELECT 'A3',
3,
1,
71
UNION ALL
SELECT 'A3',
3,
2,
0
UNION ALL
SELECT 'A3',
3,
3,
12
UNION ALL
SELECT 'A3',
3,
4,
-11
UNION ALL
SELECT 'A3',
3,
5,
-97
UNION ALL
SELECT 'A3',
4,
1,
79
UNION ALL
SELECT 'A3',
4,
2,
39
UNION ALL
SELECT 'A3',
4,
3,
81
UNION ALL
SELECT 'A3',
4,
4,
-39
UNION ALL
SELECT 'A3',
4,
5,
9
UNION ALL
SELECT 'A3',
5,
1,
-72
UNION ALL
SELECT 'A3',
5,
2,
91
UNION ALL
SELECT 'A3',
5,
3,
-84
UNION ALL
SELECT 'A3',
5,
4,
68
UNION ALL
SELECT 'A3',
5,
5,
-70
) m(id, r, c, x);
SELECT wct.NMATRIX2STRING_q('SELECT r,c,x FROM #m WHERE id = ' + CHAR(39) + 'A3'
+ CHAR(39)) as A;
This produces the following result.
{"columns":[{"field":"A"}],"rows":[{"A":"-67,-15,-67,1,26;-89,80,-8,38,100;71,0,12,-11,-97;79,39,81,-39,9;-72,91,-84,68,-70"}]}
In this example, we will produce three strings, one for each of the matrix identifiers.
SELECT n.id,
wct.NMATRIX2STRING_q('SELECT r,c,x FROM #m WHERE id = ' + CHAR(39) + n.id
+ CHAR(39)) as A
FROM
(SELECT DISTINCT id from #m) n(id);
This produces the following result.
{"columns":[{"field":"id"},{"field":"A"}],"rows":[{"id":"A1","A":"-18,98,16,-68,13;-57,-69,38,48,50;1,13,71,-21,-82;-87,57,-84,-23,-19;-11,-98,-68,38,38"},{"id":"A2","A":"55,-84,94,32,80;63,26,-12,34,73;-42,13,66,-3,87;-28,-30,-52,-23,-52;-52,-78,-47,-80,13"},{"id":"A3","A":"-67,-15,-67,1,26;-89,80,-8,38,100;71,0,12,-11,-97;79,39,81,-39,9;-72,91,-84,68,-70"}]}