CODEWITHSUNDEEP
sql-servermatrix
edgarmtze
edgarmtze

Reputation: 25058

Convert vertical to horizontal layout sql

I have a table like this in SQL ( it is not in order )

A B         VAL                  VAL2
---------------------------------------------   
1 1 -2.1846000000000e+02  0.0000000000000e+00
2 2 -2.1846000000000e+02  0.0000000000000e+00
3 2  6.4000000000000e+01  0.0000000000000e+00
1 2  6.4000000000000e+01  0.0000000000000e+00
2 3  6.4000000000000e+01  0.0000000000000e+00
3 3 -2.1846000000000e+02  0.0000000000000e+00
3 1  6.4000000000000e+01  0.0000000000000e+00
2 1  6.4000000000000e+01  0.0000000000000e+00
1 3  6.4000000000000e+01  0.0000000000000e+00

So each tuple A,B represents a point of a matrix How can i convert that into an actual matrix like 

A1                      A2                      A3
--------------------------------------------------------------------
-2.1846000000000e+02    6.4000000000000e+01     6.4000000000000e+01
6.4000000000000e+01     -2.1846000000000e+02    6.4000000000000e+01
6.4000000000000e+01     6.4000000000000e+01    -2.1846000000000e+02

Upvotes: 2

Views: 1778

Answers (2)

Denis Valeev
Denis Valeev

Reputation: 6015

Given table M (x, y, v)

SELECT x, [1], [2], [3], [4], ...
FROM
(SELECT x, y, v FROM M) AS s
PIVOT
(
min(v)
FOR y IN ([1], [2], [3], [4], ...)
) AS pvt
order by x

, ... means continue the series as needed but don't leave it in the script

Demo: http://www.sqlfiddle.com/#!3/7ee1e/17 (thanks to mellamokb for the setup script)

Upvotes: 3

mellamokb
mellamokb

Reputation: 56779

This can be done using a query like the following:

select
  max(case when B = 1 then VAL end) as A1,
  max(case when B = 2 then VAL end) as A2,
  max(case when B = 3 then VAL end) as A3
from
  MatrixData D
group by
  A;

To generate for an arbitrarily-sized matrix, you would need dynamically-generated SQL code, something like the following:

declare @sql varchar(5000)

select @sql = coalesce(@sql + ',' + char(13) + char(10), '') +
  'max(case when B = ' + cast(B as varchar(10))
    + ' then VAL end) as A' + cast(B as varchar(10))
from
  MatrixData D
group by
  B

set @sql =
  'select
    ' + @sql + '
  from
    MatrixData D
  group by
    A;'
exec(@sql)

Demo: http://www.sqlfiddle.com/#!3/7ee1e/13

Upvotes: 4

Related Questions