矩阵转置 TSQL

Question

我们可以在标准 SQL2005/2008 中进行矩阵转置（行变成列，列变成行）吗？

1 2 3 4 5
4 5 6 6 7
7 8 9 8 9
1 3 4 5 6
2 4 5 6 7

更改

1 4 7 1 2
2 5 8 3 4 
3 6 9 5 6
4 6 8 5 6
5 7 9 6 7 

行数 <>列号 ?

让我们考虑一下它固定的行数。

Answer 1

您可能想重新格式化您的问题，但如果您的数据采用以下形式，则转换很容易：

CREATE TABLE matrix (Row int NOT NULL, Column int NOT NULL, Value <datatype> NOT NULL)

SELECT Row AS Column
       ,Column AS Row
       ,Value
FROM matrix

Answer 2

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Result]') AND type in (N'U'))
DROP TABLE [dbo].[Result]
GO

CREATE TABLE [dbo].[Result](
    [RN] [int] NULL,
    [1] [int] NULL,
    [2] [int] NULL,
    [3] [int] NULL,
    [4] [int] NULL,
    [5] [int] NULL
) ON [PRIMARY]

GO

insert into Result
select 1,11,12,13,14,15
union all
select 2,21,22,23,24,25
union all
select 3,31,32,33,34,35
union all
select 4,41,42,43,44,45
union all
select 5,51,52,53,54,55

select * from Result


;WITH Preresult AS
(SELECT RN AS Row,
    Col,
    Val
FROM Result
UNPIVOT (Val FOR Col IN ([1],[2],[3],[4],[5])) unpvt
)
--select * from Preresult
--Transform array into matrix 
SELECT 
    Col as Row, 
    [1], 
    [2],
    [3],
    [4],
    [5]

FROM
    (
        SELECT Row, Col, Val FROM Preresult) t1
        PIVOT
        (MAX(Val) 
        FOR Row IN ([1],[2],[3],[4],[5])
        ) AS pvt
ORDER BY Row;-->replace Col for column sorting

来源：http://www.devx.com/dbzone/Article/ 40223/1763?supportItem=5