ssis - Transform a table in SQL -

This may be an early question but I do not know what the terminology is for conversion, so I get the useful search I'm unable to get results yet.

The input table is like:

  ID, FromCity, ToCity, ViaCity 1, New York, Chicago, Faucet 2, New York, Los Angeles, Chicago 3, Chicago, Boston , Zero   
 and desired output  
  city, FromCount, ToCount, ViaCount New York, 2, 0, Chicago, 1, 1, Los Angeles , 0, 1, 0 Boston, 0, 1, 0 zero, 0, 0, 2   
 The list of city names should already be generated from table, i.e. no existing table for this is.  
 I would like to create an indexed view but if the query is very complex and SSIS can make it easy I can also use SSIS.   
 
 
 Here is a method that is independent of the database:  
  city Select, tocity as zodiac (tocity), tocity as zodiac (tocity), through zodiac (through medium) (as the city select incity, as 1 ascity, As 0 tocity, 0 through T) union all (Select from tocity, 0     by T group 1, 0 to T) union (all , Choose 0, 0, 1 through T)