MAPPEND
Updated 2023-10-19 13:43:46.793000
Syntax
SELECT [westclintech].[wct].[MAPPEND](
<@A, nvarchar(max),>
,<@start_row_A, int,>
,<@end_row_A, int,>
,<@start_col_A, int,>
,<@end_col_A, int,>
,<@Connector, nvarchar(4000),>
,<@B, nvarchar(max),>
,<@start_row_B, int,>
,<@end_row_B, int,>
,<@start_col_B, int,>
,<@end_col_B, int,>)
Description
Use the scalar function MAPPEND to add columns or rows to an existing matrix variable. A matrix variable is a string representation of a matrix with columns separated by commas and rows separated by semi-colons. A new string representation is returned.
Arguments
@A
A string representation of the A matrix.
@end_row_B
The last row of @B to be included in the result. @end_row_B must be of a type int or of a type that implicitly converts to int.
@B
A string representation of the B matrix.
@start_row_A
The first row of @A to be included in the result. @start_row_A must be of a type int or of a type that implicitly converts to int.
@end_col_B
The last column of @B to be included in the result. @end_col_B must be of a type int or of a type that implicitly converts to int.
@start_row_B
The first row of @B to be included in the result. @start_row_B must be of a type int or of a type that implicitly converts to int.
@start_col_B
The first column of @B to be included in the result. @start_col_B must be of a type int or of a type that implicitly converts to int.
@end_col_A
The last column of @A to be included in the result. @end_col_A must be of a type int or of a type that implicitly converts to int.
@start_col_A
The first column of @A to be included in the result. @start_col_A must be of a type int or of a type that implicitly converts to int.
@Connector
Identifies the method of connection. Use ';' to append the rows of @B to the rows of @A. Use ',' to append the columns of @B to the columns of @A.
@end_row_A
The last row of @A to be included in the result. @end_row_A must be of a type int or of a type that implicitly converts to int.
Return Type
nvarchar(max)
Remarks
The string representations of @A and/or @B must only contain numbers, commas (to separate the columns), and semi-colons to separate the rows.
Consecutive commas will generate an error.
Consecutive semi-colons will generate an error.
Non-numeric data between commas will generate an error.
Non-numeric data between semi-colons will generate an error.
To convert non-normalized data to a string format, use the MATRIX2STRING or the MATRIX2STRING_q function.
To convert normalized data to a string format, us the NMATRIX2STRING or the NMATRIX2STRING_q function.
To convert the string result to a table, us the table-valued function MATRIX.
If @B is NULL then the returned matrix is @A or a subset of @A.
If @A is NULL then the returned matrix is @B or a subset of @B.
If @start_row_A is NULL then @start_row_A equals 1.
If @start_col_A is NULL then @start_col_A equals 1.
If @start_row_B is NULL then @start_row_B equals 1.
If @start_col_B is NULL then @start_col_B equals 1.
If @end_row_A is NULL then @end_row_A equals the last row in in @A.
If @end_col_A is NULL then @end_col_A equals the last row in in @A.
If @end_row_B is NULL then @end_row_B equals the last row in in @B.
If @end_col_B is NULL then @end_col_B equals the last row in in @B.
Examples
Example #1
In this example we append all the columns of B to all the columns of A.
--A,B
DECLARE @A as varchar(max) = '1,1,1,1;1,2,4,8;1,3,9,27;1,4,16,64;1,5,25,125';
DECLARE @B as varchar(max) = '1,0,0,0,0;0,1,0,0,0;0,0,1,0,0;0,0,0,1,0;0,0,0,0,1';
DECLARE @C as varchar(max) = wct.MAPPEND(@A,NULL,NULL,NULL,NULL,',',@B,NULL,NULL,NULL,NULL);
SELECT @C as [A,B];
--Only run this SQL to automatically PIVOT the results into the
--traditional row/column matrix presentation
--DECLARE @M as nvarchar(max) = N'SELECT @cols FROM wct.Matrix(@C) d PIVOT(MAX(ItemValue) FOR ColNum in (@cols))p ORDER BY RowNum';
--DECLARE @cols as nvarchar(max);
--SET @cols = (SELECT '[' + cast(ColNum as varchar(max)) + ']' FROM wct.MATRIX(@C) WHERE RowNum = 0 ORDER BY colnum FOR XML PATH(''));
--SET @cols = REPLACE(@cols,'][','],[');
--SET @M = REPLACE(REPLACE(@M,'@cols',@cols),'@C','''' + @C + '''');
--EXECUTE(@M);
This produces the following result.
{"columns":[{"field":"A,B"}],"rows":[{"A,B":"1,1,1,1,1,0,0,0,0;1,2,4,8,0,1,0,0,0;1,3,9,27,0,0,1,0,0;1,4,16,64,0,0,0,1,0;1,5,25,125,0,0,0,0,1"}]}
Here are the results formatted as a matrix.
{"columns":[{"field":"0","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"3","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"4","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"5","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"6","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"7","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"8","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"0":"1","1":"1","2":"1","3":"1","4":"1","5":"0","6":"0","7":"0","8":"0"},{"0":"1","1":"2","2":"4","3":"8","4":"0","5":"1","6":"0","7":"0","8":"0"},{"0":"1","1":"3","2":"9","3":"27","4":"0","5":"0","6":"1","7":"0","8":"0"},{"0":"1","1":"4","2":"16","3":"64","4":"0","5":"0","6":"0","7":"1","8":"0"},{"0":"1","1":"5","2":"25","3":"125","4":"0","5":"0","6":"0","7":"0","8":"1"}]}
Example #2
In this example we append all the rows of B to all the rows of A.
--A;B
DECLARE @A as varchar(max) = '1,1,1,1,1;1,2,4,8,16;1,3,9,27,81;1,4,16,64,256;1,5,25,125,625';
DECLARE @B as varchar(max) = '1,0,0,0,0;0,1,0,0,0;0,0,1,0,0;0,0,0,1,0;0,0,0,0,1';
DECLARE @C as varchar(max) = wct.MAPPEND(@A,NULL,NULL,NULL,NULL,';',@B,NULL,NULL,NULL,NULL);
SELECT @C as [A;B];
--Only run this SQL to automatically PIVOT the results into the
--traditional row/column matrix presentation
--DECLARE @M as nvarchar(max) = N'SELECT @cols FROM wct.Matrix(@C) d PIVOT(MAX(ItemValue) FOR ColNum in (@cols))p ORDER BY RowNum';
--DECLARE @cols as nvarchar(max);
--SET @cols = (SELECT '[' + cast(ColNum as varchar(max)) + ']' FROM wct.MATRIX(@C) WHERE RowNum = 0 ORDER BY colnum FOR XML PATH(''));
--SET @cols = REPLACE(@cols,'][','],[');
--SET @M = REPLACE(REPLACE(@M,'@cols',@cols),'@C','''' + @C + '''');
--EXECUTE(@M);
This produces the following result.
{"columns":[{"field":"A;B"}],"rows":[{"A;B":"1,1,1,1,1;1,2,4,8,16;1,3,9,27,81;1,4,16,64,256;1,5,25,125,625;1,0,0,0,0;0,1,0,0,0;0,0,1,0,0;0,0,0,1,0;0,0,0,0,1"}]}
Here are the results formatted as a matrix.
{"columns":[{"field":"0","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"3","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"4","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"0":"1","1":"1","2":"1","3":"1","4":"1"},{"0":"1","1":"2","2":"4","3":"8","4":"16"},{"0":"1","1":"3","2":"9","3":"27","4":"81"},{"0":"1","1":"4","2":"16","3":"64","4":"256"},{"0":"1","1":"5","2":"25","3":"125","4":"625"},{"0":"1","1":"0","2":"0","3":"0","4":"0"},{"0":"0","1":"1","2":"0","3":"0","4":"0"},{"0":"0","1":"0","2":"1","3":"0","4":"0"},{"0":"0","1":"0","2":"0","3":"1","4":"0"},{"0":"0","1":"0","2":"0","3":"0","4":"1"}]}
Example #3
In this example we return a sub-matrix of A.
--A(i:m,j:n)
DECLARE @A as varchar(max) = '1,1,1,1,1;1,2,4,8,16;1,3,9,27,81;1,4,16,64,256;1,5,25,125,625';
DECLARE @C as varchar(max) = wct.MAPPEND(@A,3,NULL,3,NULL,'',NULL,NULL,NULL,NULL,NULL);
SELECT @C as [A(i:m,j:n)];
--Only run this SQL to automatically PIVOT the results into the
--traditional row/column matrix presentation
--DECLARE @M as nvarchar(max) = N'SELECT @cols FROM wct.Matrix(@C) d PIVOT(MAX(ItemValue) FOR ColNum in (@cols))p ORDER BY RowNum';
--DECLARE @cols as nvarchar(max);
--SET @cols = (SELECT '[' + cast(ColNum as varchar(max)) + ']' FROM wct.MATRIX(@C) WHERE RowNum = 0 ORDER BY colnum FOR XML PATH(''));
--SET @cols = REPLACE(@cols,'][','],[');
--SET @M = REPLACE(REPLACE(@M,'@cols',@cols),'@C','''' + @C + '''');
--EXECUTE(@M);
This produces the following result.
{"columns":[{"field":"A(i:m,j:n)"}],"rows":[{"A(i:m,j:n)":"9,27,81;16,64,256;25,125,625"}]}
Here are the results formatted as a matrix.
{"columns":[{"field":"0","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"0":"9","1":"27","2":"81"},{"0":"16","1":"64","2":"256"},{"0":"25","1":"125","2":"625"}]}
Example #4
In this example we return a sub-matrix of B.
--B(i:m,j:n)
DECLARE @B as varchar(max) = '1,0,0,0,0;0,1,0,0,0;0,0,1,0,0;0,0,0,1,0;0,0,0,0,1';
DECLARE @C as varchar(max) = wct.MAPPEND(NULL,NULL,NULL,NULL,NULL,'',@B,3,NULL,3,NULL);
SELECT @C as [B(i:m,j:n)];
--Only run this SQL to automatically PIVOT the results into the
--traditional row/column matrix presentation
--DECLARE @M as nvarchar(max) = N'SELECT @cols FROM wct.Matrix(@C) d PIVOT(MAX(ItemValue) FOR ColNum in (@cols))p ORDER BY RowNum';
--DECLARE @cols as nvarchar(max);
--SET @cols = (SELECT '[' + cast(ColNum as varchar(max)) + ']' FROM wct.MATRIX(@C) WHERE RowNum = 0 ORDER BY colnum FOR XML PATH(''));
--SET @cols = REPLACE(@cols,'][','],[');
--SET @M = REPLACE(REPLACE(@M,'@cols',@cols),'@C','''' + @C + '''');
--EXECUTE(@M);
This produces the following result.
{"columns":[{"field":"B(i:m,j:n)"}],"rows":[{"B(i:m,j:n)":"1,0,0;0,1,0;0,0,1"}]}
Here are the results formatted as a matrix.
{"columns":[{"field":"0","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"0":"1","1":"0","2":"0"},{"0":"0","1":"1","2":"0"},{"0":"0","1":"0","2":"1"}]}
Example #5
In this example we append all of the rows but just some of the columns of A with all of the rows and just some of the columns of B. A & B must have the same number of rows.
--A(:,j:n),B(:,j:n)
DECLARE @A as varchar(max) = '1,1,1,1,1;1,2,4,8,16;1,3,9,27,81;1,4,16,64,256;1,5,25,125,625';
DECLARE @B as varchar(max) = '1,0,0,0,0;0,1,0,0,0;0,0,1,0,0;0,0,0,1,0;0,0,0,0,1';
DECLARE @C as varchar(max) = wct.MAPPEND(@A,NULL,NULL,3,NULL,';',@B,NULL,NULL,3,NULL);
SELECT @C as [A(:,j:n),B(:,j:n)];
--Only run this SQL to automatically PIVOT the results into the
--traditional row/column matrix presentation
--DECLARE @M as nvarchar(max) = N'SELECT @cols FROM wct.Matrix(@C) d PIVOT(MAX(ItemValue) FOR ColNum in (@cols))p ORDER BY RowNum'
--DECLARE @cols as nvarchar(max)
--SET @cols = (SELECT '[' + cast(ColNum as varchar(max)) + ']' FROM wct.MATRIX(@C) WHERE RowNum = 0 ORDER BY colnum FOR XML PATH(''))
--SET @cols = REPLACE(@cols,'][','],[')
--SET @M = REPLACE(REPLACE(@M,'@cols',@cols),'@C','''' + @C + '''')
--EXECUTE(@M)
This produces the following result.
{"columns":[{"field":"A(:,j:n),B(:,j:n)"}],"rows":[{"A(:,j:n),B(:,j:n)":"1,1,1;4,8,16;9,27,81;16,64,256;25,125,625;0,0,0;0,0,0;1,0,0;0,1,0;0,0,1"}]}
Here are the results formatted as a matrix.
{"columns":[{"field":"0","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"0":"1","1":"1","2":"1"},{"0":"4","1":"8","2":"16"},{"0":"9","1":"27","2":"81"},{"0":"16","1":"64","2":"256"},{"0":"25","1":"125","2":"625"},{"0":"0","1":"0","2":"0"},{"0":"0","1":"0","2":"0"},{"0":"1","1":"0","2":"0"},{"0":"0","1":"1","2":"0"},{"0":"0","1":"0","2":"1"}]}
Example #6
In this example we append all of the columns but just some of the rows of A with all of the columns and just some of the rows of B. A & B must have the same number of columns.
--A(i:m,:);B(i:m,:)
DECLARE @A as varchar(max) = '1,1,1,1,1;1,2,4,8,16;1,3,9,27,81;1,4,16,64,256;1,5,25,125,625';
DECLARE @B as varchar(max) = '1,0,0,0,0;0,1,0,0,0;0,0,1,0,0;0,0,0,1,0;0,0,0,0,1';
DECLARE @C as varchar(max) = wct.MAPPEND(@A,3,NULL,NULL,NULL,';',@B,3,NULL,NULL,NULL);
SELECT @C as [A(i:m,:);B(i:m,:)];
--Only run this SQL to automatically PIVOT the results into the
--traditional row/column matrix presentation
--DECLARE @M as nvarchar(max) = N'SELECT @cols FROM wct.Matrix(@C) d PIVOT(MAX(ItemValue) FOR ColNum in (@cols))p ORDER BY RowNum';
--DECLARE @cols as nvarchar(max);
--SET @cols = (SELECT '[' + cast(ColNum as varchar(max)) + ']' FROM wct.MATRIX(@C) WHERE RowNum = 0 ORDER BY colnum FOR XML PATH(''));
--SET @cols = REPLACE(@cols,'][','],[');
--SET @M = REPLACE(REPLACE(@M,'@cols',@cols),'@C','''' + @C + '''');
--EXECUTE(@M);
This produces the following result.
{"columns":[{"field":"A(i:m,:);B(i:m,:)"}],"rows":[{"A(i:m,:);B(i:m,:)":"1,3,9,27,81;1,4,16,64,256;1,5,25,125,625;0,0,1,0,0;0,0,0,1,0;0,0,0,0,1"}]}
Here are the results formatted as a matrix.
{"columns":[{"field":"0","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"3","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"4","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"0":"1","1":"3","2":"9","3":"27","4":"81"},{"0":"1","1":"4","2":"16","3":"64","4":"256"},{"0":"1","1":"5","2":"25","3":"125","4":"625"},{"0":"0","1":"0","2":"1","3":"0","4":"0"},{"0":"0","1":"0","2":"0","3":"1","4":"0"},{"0":"0","1":"0","2":"0","3":"0","4":"1"}]}
Example #7
In this example the selected rows and columns of B are append to the right of the selected rows and columns of A. The sub-matrices of A and B must have the same number of rows.
--A(i:m,j:n),B(k:p,l:q)
DECLARE @A as varchar(max) = '1,1,1,1,1;1,2,4,8,16;1,3,9,27,81;1,4,16,64,256;1,5,25,125,625';
DECLARE @B as varchar(max) = '1,0,0,0,0;0,1,0,0,0;0,0,1,0,0;0,0,0,1,0;0,0,0,0,1';
DECLARE @C as varchar(max) = wct.MAPPEND(@A,2,5,1,4,',',@B,1,4,4,4);
SELECT @C as [A(i:m,j:n),B(k:p,l:q)];
--Only run this SQL to automatically PIVOT the results into the
--traditional row/column matrix presentation
--DECLARE @M as nvarchar(max) = N'SELECT @cols FROM wct.Matrix(@C) d PIVOT(MAX(ItemValue) FOR ColNum in (@cols))p ORDER BY RowNum';
--DECLARE @cols as nvarchar(max);
--SET @cols = (SELECT '[' + cast(ColNum as varchar(max)) + ']' FROM wct.MATRIX(@C) WHERE RowNum = 0 ORDER BY colnum FOR XML PATH(''));
--SET @cols = REPLACE(@cols,'][','],[');
--SET @M = REPLACE(REPLACE(@M,'@cols',@cols),'@C','''' + @C + '''');
--EXECUTE(@M);
This produces the following result.
{"columns":[{"field":"A(i:m,j:n),B(k:p,l:q)"}],"rows":[{"A(i:m,j:n),B(k:p,l:q)":"1,2,4,8,0;1,3,9,27,0;1,4,16,64,0;1,5,25,125,1"}]}
Here are the results formatted as a table.
{"columns":[{"field":"0","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"3","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"4","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"0":"1","1":"2","2":"4","3":"8","4":"0"},{"0":"1","1":"3","2":"9","3":"27","4":"0"},{"0":"1","1":"4","2":"16","3":"64","4":"0"},{"0":"1","1":"5","2":"25","3":"125","4":"1"}]}
Example #8
In this example the selected rows and columns of B are appended to the bottom of the selected rows and columns of A. The sub-matrices of A and B must have the same number of columns.
--A(i:m,j:n);B(k:p,l:q)
DECLARE @A as varchar(max) = '1,1,1,1,1;1,2,4,8,16;1,3,9,27,81;1,4,16,64,256;1,5,25,125,625';
DECLARE @B as varchar(max) = '1,0,0,0,0;0,1,0,0,0;0,0,1,0,0;0,0,0,1,0;0,0,0,0,1';
DECLARE @C as varchar(max) = wct.MAPPEND(@A,3,5,1,4,';',@B,1,1,1,4);
SELECT @C as [A(i:m,j:n);B(k:p,l:q)];
--Only run this SQL to automatically PIVOT the results into the
--traditional row/column matrix presentation
--DECLARE @M as nvarchar(max) = N'SELECT @cols FROM wct.Matrix(@C) d PIVOT(MAX(ItemValue) FOR ColNum in (@cols))p ORDER BY RowNum';
--DECLARE @cols as nvarchar(max);
--SET @cols = (SELECT '[' + cast(ColNum as varchar(max)) + ']' FROM wct.MATRIX(@C) WHERE RowNum = 0 ORDER BY colnum FOR XML PATH(''));
--SET @cols = REPLACE(@cols,'][','],[');
--SET @M = REPLACE(REPLACE(@M,'@cols',@cols),'@C','''' + @C + '''');
--EXECUTE(@M);
This produces the following result.
{"columns":[{"field":"A(i:m,j:n);B(k:p,l:q)"}],"rows":[{"A(i:m,j:n);B(k:p,l:q)":"1,3,9,27;1,4,16,64;1,5,25,125;1,0,0,0"}]}
Here are the results formatted as a table.
{"columns":[{"field":"0","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"3","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"0":"1","1":"3","2":"9","3":"27"},{"0":"1","1":"4","2":"16","3":"64"},{"0":"1","1":"5","2":"25","3":"125"},{"0":"1","1":"0","2":"0","3":"0"}]}