Login     Register

        Contact Us     Search

XLeratorDB/math Documentation

SQL Server TRANSPOSE Function


TRANSPOSE

Updated: 31 January 2012


Use the scalar function TRANSPOSE to return the transposed matrix. 
TRANSPOSE expects a string representation of the matrix, with columns separated by commas and rows separated by semi-colons.
Syntax
SELECT [wctMath].[wct].[TRANSPOSE](
   <@Matrix, nvarchar(max),>)
Arguments
@Matrix
a string representation of a matrix.
Return Types
[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.
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.StringSegment as [A']
FROM wctString.wct.SPLIT(';',(SELECT wct.TRANSPOSE(@A))) l
This produces the following result:
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
 
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((SELECT wct.TRANSPOSE(@A))) l
This produces the following result.
     RowNum      ColNum              ItemValue
----------- ----------- ----------------------
          0           0                    -79
          0           1                     68
          0           2                     83
          0           3                     77
          0           4                    -17
          0           5                     64
          0           6                     -4
          0           7                    -41
          0           8                    -60
          0           9                    -40
          1           0                    -45
          1           1                     46
          1           2                    -25
          1           3                     40
          1           4                    -72
          1           5                    -47
          1           6                    -36
          1           7                     90
          1           8                    -85
          1           9                     31
          2           0                      9
          2           1                      9
          2           2                     80
          2           3                    -24
          2           4                     -9
          2           5                     48
          2           6                      7
          2           7                     78
          2           8                    -31
          2           9                    -93
          3           0                      9
          3           1                     81
          3           2                    -67
          3           3                     69
          3           4                    -72
          3           5                    -54
          3           6                    -56
          3           7                    -43
          3           8                    -83
          3           9                    -62
          4           0                    -91
          4           1                    -61
          4           2                    -22
          4           3                     73
          4           4                     -6
          4           5                     18
          4           6                    -34
          4           7                     38
          4           8                    -96
          4           9                     64
          5           0                     -5
          5           1                     35
          5           2                    -38
          5           3                    -20
          5           4                    -34
          5           5                     11
          5           6                     -3
          5           7                     64
          5           8                    -36
          5           9                     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((SELECT 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.


 
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((SELECT 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.


 
In this example, we insert the matrix values into a table, #m, which is in ‘spreadsheet’ format, and we use the MATRIX2SRTING 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((SELECT 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.
 


Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service