--select data from sales1

SELECT * FROM sales1 ORDER BY 1,2;

--pivot on the quarter to return sales for each quarter by country, state, and year

SELECT * FROM SALES1 PIVOT(SUM(sales) FOR qtr IN ('Q1','Q2','Q3','Q4')) dt1 
ORDER BY 1,2,3;

-- modify this same query by providing an alias in our sql statement for the aggregate column sales, 
-- and for each quarter value in the IN list. 

SELECT * FROM sales1 PIVOT(SUM(sales) as sales FOR qtr IN ('Q1' AS Q1,'Q2' AS Q2,'Q3' AS Q3,'Q4' AS Q4)) dt1 
ORDER BY 1,2,3;
  
--select data from sales2

SELECT * FROM sales2 order by 1,2;

--pivot on quarter to return quarterly sales columns by country, state, and year, and also return the quarterly total of units sold

SELECT * FROM sales2 PIVOT (SUM(sales) as sales, SUM(units) as units FOR qtr IN ('Q1' AS Qrt1, 'Q2' AS Qrt2, 'Q3' AS Qrt3))Tmp;  
			
-- pivot on quarter and year to return quarterly sales columns by country, state, and year, 
-- and also return the quarterly total of units sold

SELECT * FROM sales2 PIVOT (SUM(sales) AS sales, SUM(units) AS units FOR (yr, qtr) 
IN (('2019', 'Q1'), ('2019', 'Q2'),('2019', 'Q3')) )Tmp;