sql-server - 将垂直布局转换为水平布局sql

Question

我在 SQL 中有一个这样的表(它没有顺序)

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

所以每个元组A,B代表矩阵的一个点我怎样才能把它转换成一个像

这样的实际矩阵

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 

Answer 1

这可以使用如下查询来完成:

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;

要生成任意大小的矩阵，您需要动态生成的 SQL 代码，如下所示:

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)

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