如何创建SQL矩阵(How to create a matrix with SQL)

有了这样的地理数据记录：

START                  |  END

CITY1    |   STATE1    |   CITY2    |  STATE2
----------------------------------------------
New York |    NY       |  Boston    |   MA
Newark   |    NJ       |  Albany    |   NY
Cleveland|    OH       |  Cambridge |   MA

我想输出是这样重要的地方显示为矩阵开始/结束配对：

   |  MA  |  NJ  |  NY  |  OH
------------------------------
MA |  0   |  0   |  1   |  0
NJ |  0   |  0   |  1   |  0
NY |  1   |  0   |  0   |  0
OH |  1   |  0   |  0   |  0

我可以看到GROUP BY和COUNT会发现数据，但我丢了怎么显示为矩阵上。 有没有人有什么想法？

Answer 1:

这似乎这样的伎俩，PostgreSQL的9.1测试。 它几乎肯定会需要适应的SQL Server（任何人随时更新我回答这个效果）。

SELECT start AS state,
    SUM((dest = 'MA')::INT) AS MA,
    SUM((dest = 'NJ')::INT) AS NJ,
    SUM((dest = 'NY')::INT) AS NY,
    SUM((dest = 'OH')::INT) AS OH
FROM (
    SELECT state1 AS start, state2 AS dest
        FROM routes
    UNION ALL
    SELECT state2 AS start, state1 AS dest
        FROM routes
) AS s
GROUP BY start
ORDER BY start;

但是请注意，我的输出略有不同比你 - 我不知道这是因为你的样本输出是错误的，或者是因为我误解你的要求：

 state | ma | nj | ny | oh 
-------+----+----+----+----
 MA    |  0 |  0 |  1 |  1
 NJ    |  0 |  0 |  1 |  0
 NY    |  1 |  1 |  0 |  0
 OH    |  1 |  0 |  0 |  0
(4 rows)

此查询工作通过查询该表两次，一次为状态1 - >状态2路线，并为状态2第二次- >状态1的路线，然后一起加入他们UNION ALL 。

然后，每个目的地国家，它运行一个SUM()该行的起源状态。

这种策略应该很容易对任何RDBMS适应。

文章来源: How to create a matrix with SQL