How can I make a Matrix Transposition in SQL ?

How can I make a Matrix Transposition in SQL ?

• yass1400

  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

  
  
  

• Larry Kyrala

  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 :)

  
  
  

• MGray

  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!

  
  
  

• Eze

  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

  
  
  

• mrdomiscoding

  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.
  
  

• tsukelly

  You can use UNION ALL in the SELECT statement to avoid the distinct operation which will perform better also.
  
  
• How can I make a Matrix Transposition in SQL ?