Matrix Transpose TSQL

Question

Can we do matrix transpose (rows become columns and columns become rows) in standard 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

changes to

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 

how about no of rows <> no of column ?

let's consider the no of rows it's fixed.

Answer 1

You might want to reformat your question, but a transform is easy if your data is in the form:

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

What version of SQL-server are you using? If it's 2005, or later, you can use the UNPIVOT operator to transpose.

See, this article, for example. Or just Google it.

Answer 3

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

Source: http://www.devx.com/dbzone/Article/40223/1763?supportItem=5