NMATRIX2STRING
Updated 2023-10-19 15:50:32.313000
Syntax
SELECT [westclintech].[wct].[NMATRIX2STRING] (
<@Row, int,>
,<@Col, int,>
,<@Value, float,>)
Description
Use the aggregate function NMATRIX2STRING to turn third-normal form 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
@Row
The name of the column which identifies the row of the matrix. @Row values must evaluate to a type of int or of a type that implicitly converts to int.
@Val
The name of the column which identifies the value at the row-column location of the matrix. @Val values must evaluate to a type of float or of a type that implicitly converts to float.
@Col
The name of the column which identifies the column of the matrix. @Col values must evaluate to a type of int or of a type that implicitly converts to int.
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(r, c, x) as A
FROM #m;
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(r, c, x) as A
FROM #m
WHERE r > 2
AND c > 2;
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(r, 1, x) as A
FROM #m
WHERE r = c;
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(r, c, x) as A
FROM #m
WHERE id = 'A3';
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 id,
wct.NMATRIX2STRING(r, c, x) as A
FROM #m
GROUP BY 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"}]}