How can I make a Matrix Transposition in SQL ?

Can we do matrix transpose (rows become columns and columns become rows) in standard SQL?

1 2 3
4 5 6
7 8 9

changes to

1 4 7
2 5 8
3 6 9

how about the situation when no of rows <> no of column ?

let's consider the no of rows it's fixed and known before running the SQL statement.

thanks.

What do you mean standard SQL? Here is a 2005 version that will rotate your data (it is not as elegant without CTE's, UNPIVOT and PIVOT, but it can be done if you need it:

The basic idea used was to add a key for the rotate, and then do an UNPIVOT followed by a PIVOT:

set nocount on
create table pivotTest
(
pkey varchar(10) primary key,
col1 varchar(10),
col2 varchar(10),
col3 varchar(10)
)

insert into pivotTest (pkey, col1, col2, col3)
select 'a','1','2','3'
union all
select 'b','4','5','6'
union all
select 'c','7','8','9'
go

select *
from pivotTest
go

This returns:

pkey col1 col2 col3
- - - -
a 1 2 3
b 4 5 6
c 7 8 9

First take the set and flatten it out:

with breakdown as( --cte instead of temp table or derived table
--unpivot
select pkey, cast(name as varchar(20)) as name, value
from ( select pkey, col1, col2, col3
from pivotTest) p
UNPIVOT
(value for name in (col1, col2, col3)) as unpvt)
select *
from breakdown

returns:

pkey name value
- -- -
a col1 1
a col2 2
a col3 3
b col1 4
b col2 5
b col3 6
c col1 7
c col2 8
c col3 9

Then rotate it with pivot on the pkey values (see --section repivot)

with breakdown as(
--unpivot
select pkey, cast(name as varchar(20)) as name, value
from ( select pkey, col1, col2, col3
from pivotTest) p
UNPIVOT
(value for name in (col1, col2, col3)) as unpvt)

--repivot
select cast(name as varchar(10)), a,b,c
from
(select name, pkey,value
from breakdown) as rotated
PIVOT
(
MAX(value)
for pkey in (a,b,c)) as pvt --this was ,,

name a b c
- - - -
col1 1 4 7
col2 2 5 8
col3 3 6 9

--clean up

drop table pivotTest
go

|||

Here is a Standard SQL Version ;)

--Use sample data provided by Louis

SELECT id, MIN(CASE WHEN P.pkey = 'a' THEN col1 END) AS Col1,
MIN(CASE WHEN P.pkey = 'b' THEN col1 END) AS Col2,
MIN(CASE WHEN P.pkey = 'c' THEN col1 END) AS Col3
FROM
(SELECT 'Col 1' as id,pkey, col1 FROM pivotTest
UNION
SELECT 'Col 2', pkey, col2 FROM pivotTest
UNION
SELECT 'Col 3',pkey, col3 FROM pivotTest) P
GROUP BY id
ORDER BY id

Regards
Roji. P. Thomas

|||You can use UNION ALL in the SELECT statement to avoid the distinct operation which will perform better also.|||

I know you specified "with standard SQL", but it is worth noting that if you do matrix operations using SQL Server 2005, it might be worth investigating if CLR UDTs would be a good fit.

Along with simpler programmability, you would most likely get better performance, too!

|||

I kind of doubt that it would be that much simpler, and I don't even know about better performance (though that one is probably much more likely)

If you want to build it, I would certainly be willing to help out by giving it a run for its money and build a large enough test case to see :)

|||Well, there are several linear algebra packages available for C# on the web - I haven't tried them myself, but they might be worth checking out.