CODEWITHSUNDEEP
sqlssis
NS.X.
NS.X.

Reputation: 2142

Transform a table in SQL

This might be a beginner's question but I don't know what's the terminology for the transformation so I am unable to get useful search results so far.

The input table is like:

ID, FromCity, ToCity, ViaCity
1, New York, Chicago, NULL
2, New York, Los Angeles, Chicago
3, Chicago, Boston, NULL

And the desired output is

City, FromCount, ToCount, ViaCount
New York, 2, 0, 0
Chicago, 1, 1, 1
Los Angeles, 0, 1, 0
Boston, 0, 1, 0
NULL, 0, 0, 2

The list of city names should be generated from the first table, i.e. there isn't an existing table for it.

I would prefer to build an indexed view but if the query is too complex and SSIS can make it easy I can use SSIS too.

Upvotes: 0

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Here is a method that is database independent:

select city, sum(fromcity) as fromcity, sum(tocity) as tocity, sum(via) as via
from ((select fromcity as city, 1 as fromcity, 0 as tocity, 0 a via
       from t
      ) union all
      (select tocity, 0, 1, 0
       from t
      ) union all
      (select via, 0, 0, 1
       from t
      )
     ) t
group by city

Upvotes: 1

Related Questions