SQL Server TRANSPOSE Function

Updated 2023-10-17 16:06:23.917000

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.

Syntax

SELECT [westclintech].[wctMath].[wct].[TRANSPOSE](
   <@Matrix, nvarchar(max),>)

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.

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:

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.

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.

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.

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.