How to create a matrix with SQL

Question

With these geographic records:

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

I would like to output something like this when it considers the START / END pairs displayed as a matrix:

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

I see how GROUP BY and COUNT will find the data, but I'm lost in how to display them as a matrix. Does anyone have any ideas?

Answer 1

This is similar to a trick tested on PostgreSQL 9.1. It will almost certainly need to be adapted for SQL Server (anyone can edit my answer to this question).

 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; 

However, please note that my result is slightly different from yours. I am not sure if this is because your sample result is incorrect or because I misunderstood your requirements:

  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) 

This query works by querying the table twice, once for routes state1 → state2 and a second time for states state2 → state1, and then combines them together with UNION ALL .

Then, for each destination state, SUM() is executed for that beginning of line state.

This strategy should be easily adapted for any DBMS.