将垂直转换为水平布局sql [英] Convert vertical to horizontal layout sql

问题描述

我在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

所以每个元组A，B代表矩阵的一个点 我如何将其转换为实际的矩阵，如

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 

推荐答案

给出表M(x，y，v)

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

演示: http://www.sqlfiddle.com/#!3/7ee1e/17 (感谢mellamokb的安装脚本)

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

这篇关于将垂直转换为水平布局sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！