Create View with date in one table-CodePudding

I have a table data as below 'table 1', would like to create a view as below 'view1' with the table 1, was that possible?

Table 1

Code	Outletname	Date	Total
A	Outlet A	01/09/2022	10
A	Outlet A	02/09/2022	20
B	Outlet B	01/09/2022	30
B	Outlet B	02/09/2022	40

Date	Outlet A Total	Outlet B Total
01/09/2022	10	30
02/09/2022	20	40

CodePudding user response：

WITH totals AS
(  -- get the total info by outlet and date
   SELECT outletname, date, sum(total) as total
   FROM table1
   GROUP BY outletname, date
), dates AS
(  -- get list of all the dates
   SELECT date 
   FROM table1
   GROUP BY date
) -- use left joins to get the results
SELECT dates.date, ja.total as [outlet a total], jb.total as [outlet b total] 
FROM dates
LEFT JOIN totals ja on dates.date = j1.date and outletname = 'Outlet A'
LEFT JOIN totals jb on dates.date = j1.date and outletname = 'Outlet B'