TRANSPOSE
Updated 2023-10-17 16:06:23.917000
Syntax
SELECT [westclintech].[wctMath].[wct].[TRANSPOSE](
<@Matrix, nvarchar(max),>)
Description
Use the scalar function TRANSPOSE to return the matrix transpose. TRANSPOSE expects a string representation of the matrix, with columns separated by commas and rows separated by semi-colons.
Arguments
@Matrix
a string representation of a matrix.
Return Type
nvarchar(max)
Remarks
The string representations of @Matrix 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-number 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, use the NMatrix2String or the NMatrix2String_q function.
Examples
Let’s assume that we had the following matrix, A, and we want to return the transposed matrix A’.
A = [-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]
We could enter the following SQL to perform the calculation.
DECLARE @A as varchar(max);
SET @A
= '-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';
SELECT wct.TRANSPOSE(@A) as [A'];
This produces the following result.
{"columns":[{"field":"A'"}],"rows":[{"A'":"-79,68,83,77,-17,64,-4,-41,-60,-40;-45,46,-25,40,-72,-47,-36,90,-85,31;9,9,80,-24,-9,48,7,78,-31,-93;9,81,-67,69,-72,-54,-56,-43,-83,-62;-91,-61,-22,73,-6,18,-34,38,-96,64;-5,35,-38,-20,-34,11,-3,64,-36,10"}]}
Of course, this is a little hard to read. Since the result is a string, we can reformat the solution to make it easier to read. Simply by changing the SELECT statement:
SELECT l.item as [A']
FROM wct.SPLIT(wct.TRANSPOSE(@A), ';') l;
This produces the following result:
{"columns":[{"field":"A'"}],"rows":[{"A'":"-79,68,83,77,-17,64,-4,-41,-60,-40"},{"A'":"-45,46,-25,40,-72,-47,-36,90,-85,31"},{"A'":"9,9,80,-24,-9,48,7,78,-31,-93"},{"A'":"9,81,-67,69,-72,-54,-56,-43,-83,-62"},{"A'":"-91,-61,-22,73,-6,18,-34,38,-96,64"},{"A'":"-5,35,-38,-20,-34,11,-3,64,-36,10"}]}
Which is a little bit easier to follow.
However, we can use the table-valued function MATRIX, to format the result in third-normal form where it is even easier to see the output.
SELECT *
FROM wct.MATRIX(wct.TRANSPOSE(@A)) l;
This produces the following result.
{"columns":[{"field":"RowNum","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"ColNum","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"ItemValue","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"RowNum":"0","ColNum":"0","ItemValue":"-79"},{"RowNum":"0","ColNum":"1","ItemValue":"68"},{"RowNum":"0","ColNum":"2","ItemValue":"83"},{"RowNum":"0","ColNum":"3","ItemValue":"77"},{"RowNum":"0","ColNum":"4","ItemValue":"-17"},{"RowNum":"0","ColNum":"5","ItemValue":"64"},{"RowNum":"0","ColNum":"6","ItemValue":"-4"},{"RowNum":"0","ColNum":"7","ItemValue":"-41"},{"RowNum":"0","ColNum":"8","ItemValue":"-60"},{"RowNum":"0","ColNum":"9","ItemValue":"-40"},{"RowNum":"1","ColNum":"0","ItemValue":"-45"},{"RowNum":"1","ColNum":"1","ItemValue":"46"},{"RowNum":"1","ColNum":"2","ItemValue":"-25"},{"RowNum":"1","ColNum":"3","ItemValue":"40"},{"RowNum":"1","ColNum":"4","ItemValue":"-72"},{"RowNum":"1","ColNum":"5","ItemValue":"-47"},{"RowNum":"1","ColNum":"6","ItemValue":"-36"},{"RowNum":"1","ColNum":"7","ItemValue":"90"},{"RowNum":"1","ColNum":"8","ItemValue":"-85"},{"RowNum":"1","ColNum":"9","ItemValue":"31"},{"RowNum":"2","ColNum":"0","ItemValue":"9"},{"RowNum":"2","ColNum":"1","ItemValue":"9"},{"RowNum":"2","ColNum":"2","ItemValue":"80"},{"RowNum":"2","ColNum":"3","ItemValue":"-24"},{"RowNum":"2","ColNum":"4","ItemValue":"-9"},{"RowNum":"2","ColNum":"5","ItemValue":"48"},{"RowNum":"2","ColNum":"6","ItemValue":"7"},{"RowNum":"2","ColNum":"7","ItemValue":"78"},{"RowNum":"2","ColNum":"8","ItemValue":"-31"},{"RowNum":"2","ColNum":"9","ItemValue":"-93"},{"RowNum":"3","ColNum":"0","ItemValue":"9"},{"RowNum":"3","ColNum":"1","ItemValue":"81"},{"RowNum":"3","ColNum":"2","ItemValue":"-67"},{"RowNum":"3","ColNum":"3","ItemValue":"69"},{"RowNum":"3","ColNum":"4","ItemValue":"-72"},{"RowNum":"3","ColNum":"5","ItemValue":"-54"},{"RowNum":"3","ColNum":"6","ItemValue":"-56"},{"RowNum":"3","ColNum":"7","ItemValue":"-43"},{"RowNum":"3","ColNum":"8","ItemValue":"-83"},{"RowNum":"3","ColNum":"9","ItemValue":"-62"},{"RowNum":"4","ColNum":"0","ItemValue":"-91"},{"RowNum":"4","ColNum":"1","ItemValue":"-61"},{"RowNum":"4","ColNum":"2","ItemValue":"-22"},{"RowNum":"4","ColNum":"3","ItemValue":"73"},{"RowNum":"4","ColNum":"4","ItemValue":"-6"},{"RowNum":"4","ColNum":"5","ItemValue":"18"},{"RowNum":"4","ColNum":"6","ItemValue":"-34"},{"RowNum":"4","ColNum":"7","ItemValue":"38"},{"RowNum":"4","ColNum":"8","ItemValue":"-96"},{"RowNum":"4","ColNum":"9","ItemValue":"64"},{"RowNum":"5","ColNum":"0","ItemValue":"-5"},{"RowNum":"5","ColNum":"1","ItemValue":"35"},{"RowNum":"5","ColNum":"2","ItemValue":"-38"},{"RowNum":"5","ColNum":"3","ItemValue":"-20"},{"RowNum":"5","ColNum":"4","ItemValue":"-34"},{"RowNum":"5","ColNum":"5","ItemValue":"11"},{"RowNum":"5","ColNum":"6","ItemValue":"-3"},{"RowNum":"5","ColNum":"7","ItemValue":"64"},{"RowNum":"5","ColNum":"8","ItemValue":"-36"},{"RowNum":"5","ColNum":"9","ItemValue":"10"}]}
And, if we wanted to see the result in a row/column presentation, we could use the following SQL.
SELECT [0],
[1],
[2],
[3],
[4],
[5],
[6],
[7],
[8],
[9]
FROM
(SELECT * FROM wct.MATRIX(wct.TRANSPOSE(@A)) ) M
PIVOT
(
MAX(ItemValue)
FOR colnum IN ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9])
) AS pvt
ORDER BY rownum;
This produces the following result.
{"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"},{"field":"9","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"0":"-79","1":"68","2":"83","3":"77","4":"-17","5":"64","6":"-4","7":"-41","8":"-60","9":"-40"},{"0":"-45","1":"46","2":"-25","3":"40","4":"-72","5":"-47","6":"-36","7":"90","8":"-85","9":"31"},{"0":"9","1":"9","2":"80","3":"-24","4":"-9","5":"48","6":"7","7":"78","8":"-31","9":"-93"},{"0":"9","1":"81","2":"-67","3":"69","4":"-72","5":"-54","6":"-56","7":"-43","8":"-83","9":"-62"},{"0":"-91","1":"-61","2":"-22","3":"73","4":"-6","5":"18","6":"-34","7":"38","8":"-96","9":"64"},{"0":"-5","1":"35","2":"-38","3":"-20","4":"-34","5":"11","6":"-3","7":"64","8":"-36","9":"10"}]}
The matrix does not have to be assigned to a variable before passed into the TRANSPOSE function; the string can be passed in directly.
SELECT [0],
[1],
[2],
[3],
[4],
[5],
[6],
[7],
[8],
[9]
FROM
(
SELECT *
FROM wct.MATRIX(wct.TRANSPOSE('-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
PIVOT
(
MAX(ItemValue)
FOR colnum IN ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9])
) AS pvt
ORDER BY rownum;
This produces the following result.
{"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"},{"field":"9","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"0":"-79","1":"68","2":"83","3":"77","4":"-17","5":"64","6":"-4","7":"-41","8":"-60","9":"-40"},{"0":"-45","1":"46","2":"-25","3":"40","4":"-72","5":"-47","6":"-36","7":"90","8":"-85","9":"31"},{"0":"9","1":"9","2":"80","3":"-24","4":"-9","5":"48","6":"7","7":"78","8":"-31","9":"-93"},{"0":"9","1":"81","2":"-67","3":"69","4":"-72","5":"-54","6":"-56","7":"-43","8":"-83","9":"-62"},{"0":"-91","1":"-61","2":"-22","3":"73","4":"-6","5":"18","6":"-34","7":"38","8":"-96","9":"64"},{"0":"-5","1":"35","2":"-38","3":"-20","4":"-34","5":"11","6":"-3","7":"64","8":"-36","9":"10"}]}
In this example, we insert the matrix values into a table, #m, which is in ‘spreadsheet’ format, and we use the MATRIX2STIING function to convert the table values into a string format to be used by the TRANSPOSE function.
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);
SELECT [0],
[1],
[2],
[3],
[4],
[5],
[6],
[7],
[8],
[9]
FROM
(
SELECT *
FROM wct.MATRIX(wct.TRANSPOSE(wct.MATRIX2STRING('#m', '*', '', NULL)))
) M
PIVOT
(
MAX(ItemValue)
FOR colnum IN ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9])
) AS pvt
ORDER BY rownum;
This produces the following result.
{"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"},{"field":"9","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"0":"-79","1":"68","2":"83","3":"77","4":"-17","5":"64","6":"-4","7":"-41","8":"-60","9":"-40"},{"0":"-45","1":"46","2":"-25","3":"40","4":"-72","5":"-47","6":"-36","7":"90","8":"-85","9":"31"},{"0":"9","1":"9","2":"80","3":"-24","4":"-9","5":"48","6":"7","7":"78","8":"-31","9":"-93"},{"0":"9","1":"81","2":"-67","3":"69","4":"-72","5":"-54","6":"-56","7":"-43","8":"-83","9":"-62"},{"0":"-91","1":"-61","2":"-22","3":"73","4":"-6","5":"18","6":"-34","7":"38","8":"-96","9":"64"},{"0":"-5","1":"35","2":"-38","3":"-20","4":"-34","5":"11","6":"-3","7":"64","8":"-36","9":"10"}]}