USE OLAP
--Original Query
SELECT c.country, s.order_method_type,s.revenue
FROM sales_enriched as s JOIN countries as c ON s.retailer_city=c.city

--Create matrix
SELECT country --column for our country dimension
	   ,[Web],[E-mail],[Mail],[Telephone],[Sales visit] --headings for our order method types
FROM (SELECT c.country, s.order_method_type,s.revenue
      FROM sales_enriched as s JOIN countries as c ON s.retailer_city=c.city) as original_query
PIVOT (sum(revenue)--sum the revenue
	   FOR order_method_type IN ([Web],[E-mail],[Mail],[Telephone],[Sales visit]) 
	   --for every order method type listed above
	   --this should correspond to the headings we defined in the select statement above.
	   ) as pivot_table
--any columns not mentioned in the PIVOT clause such as country are unaffected.

--Make a view
CREATE VIEW matrix_report AS 
SELECT country --column for our country dimension
	   ,[Web],[E-mail],[Mail],[Telephone],[Sales visit] --headings for our order method types
FROM (SELECT c.country, s.order_method_type,s.revenue
      FROM sales_enriched as s JOIN countries as c ON s.retailer_city=c.city) as original_query
PIVOT (sum(revenue)--sum the revenue
	   FOR order_method_type IN ([Web],[E-mail],[Mail],[Telephone],[Sales visit]) 
	   --for every order method type listed above
	   --this should correspond to the headings we d,efined in the select statement above.
	   ) as pivot_table

--Missing a column in the final SELECT
SELECT country --column for our country dimension
	   ,[Web],[E-mail],[Mail],[Telephone] --headings for our order method types
FROM (SELECT c.country, s.order_method_type,s.revenue
      FROM sales_enriched as s JOIN countries as c ON s.retailer_city=c.city) as original_query
PIVOT (sum(revenue)--sum the revenue
	   FOR order_method_type IN ([Web],[E-mail],[Mail],[Telephone],[Sales visit]) 
	   --for every order method type listed above
	   --this should correspond to the headings we defined in the select statement above.
	   ) as pivot_table

--Missing column in the PIVOT command
SELECT country --column for our country dimension
	   ,[Web],[E-mail],[Mail],[Telephone],[Sales visit] --headings for our order method types
FROM (SELECT c.country, s.order_method_type,s.revenue
      FROM sales_enriched as s JOIN countries as c ON s.retailer_city=c.city) as original_query
PIVOT (sum(revenue)--sum the revenue
	   FOR order_method_type IN ([Web],[E-mail],[Mail],[Telephone])--,[Sales visit]) 
	   --for every order method type listed above
	   --this should correspond to the headings we defined in the select statement above.
	   ) as pivot_table

--Names don't match
SELECT country --column for our country dimension
	   ,[Web],[E-mail],[Mail],[Telephone],[Different Name] --headings for our order method types
FROM (SELECT c.country, s.order_method_type,s.revenue
      FROM sales_enriched as s JOIN countries as c ON s.retailer_city=c.city) as original_query
PIVOT (sum(revenue)--sum the revenue
	   FOR order_method_type IN ([Web],[E-mail],[Mail],[Telephone],[Sales visit]) 
	   --for every order method type listed above
	   --this should correspond to the headings we defined in the select statement above.
	   ) as pivot_table

--Include city
SELECT country --column for our country dimension
       ,city --add city here
	   ,[Web],[E-mail],[Mail],[Telephone],[Sales visit] --headings for our order method types
FROM (SELECT c.country, c.city, s.order_method_type,s.revenue--include city in subquery
      FROM sales_enriched as s JOIN countries as c ON s.retailer_city=c.city) as original_query
PIVOT (sum(revenue)--sum the revenue
	   FOR order_method_type IN ([Web],[E-mail],[Mail],[Telephone],[Sales visit]) 
	   --for every order method type listed above
	   --this should correspond to the headings we defined in the select statement above.
	   ) as pivot_table

--Order by country,city
SELECT country --column for our country dimension
       ,city --add city here
	   ,[Web],[E-mail],[Mail],[Telephone],[Sales visit] --headings for our order method types
FROM (SELECT c.country, c.city, s.order_method_type,s.revenue--include city in subquery
      FROM sales_enriched as s JOIN countries as c ON s.retailer_city=c.city) as original_query
PIVOT (sum(revenue)--sum the revenue
	   FOR order_method_type IN ([Web],[E-mail],[Mail],[Telephone],[Sales visit]) 
	   --for every order method type listed above
	   --this should correspond to the headings we defined in the select statement above.
	   ) as pivot_table
ORDER BY country,city

--Order by Web sales
SELECT country --column for our country dimension
       ,city --add city here
	   ,[Web],[E-mail],[Mail],[Telephone],[Sales visit] --headings for our order method types
FROM (SELECT c.country, c.city, s.order_method_type,s.revenue--include city in subquery
      FROM sales_enriched as s JOIN countries as c ON s.retailer_city=c.city) as original_query
PIVOT (sum(revenue)--sum the revenue
	   FOR order_method_type IN ([Web],[E-mail],[Mail],[Telephone],[Sales visit]) 
	   --for every order method type listed above
	   --this should correspond to the headings we defined in the select statement above.
	   ) as pivot_table
ORDER BY Web

--To order the columns you need to manually change the order in BOTH the subquery and
--The main query.
SELECT country
       ,city
	   ,[E-mail],[Mail],[Sales visit],[Telephone],[Web] --Change order here and...

FROM (SELECT c.country, c.city, s.order_method_type,s.revenue
      FROM sales_enriched as s JOIN countries as c ON s.retailer_city=c.city) as original_query
PIVOT (sum(revenue)
	   FOR order_method_type IN (
								 [E-mail],[Mail],[Sales visit],[Telephone],[Web]--...here
							     ) 
	   --for every order method type listed above
	   --this should correspond to the headings we defined in the select statement above.
	   ) as pivot_table
ORDER BY country,city