基于含日期字段的单表创建指定结构视图的可行性问询

完全可行，这属于**行转列（透视表）**的典型场景，以下是主流关系型数据库的落地实现方案：

通用兼容方案（适配MySQL、PostgreSQL、SQL Server等） #

使用CASE WHEN配合分组聚合实现，兼容性最强，创建视图的SQL语句如下：

CREATE VIEW view1 AS
SELECT
    Date,
    SUM(CASE WHEN Outletname = 'Outlet A' THEN Total ELSE 0 END) AS `Outlet A Total`,
    SUM(CASE WHEN Outletname = 'Outlet B' THEN Total ELSE 0 END) AS `Outlet B Total`
FROM table1
GROUP BY Date
ORDER BY Date;

特定数据库简化方案 #

SQL Server / Azure SQL #

可使用PIVOT语法简化代码：

CREATE VIEW view1 AS
SELECT
    Date,
    [Outlet A] AS `Outlet A Total`,
    [Outlet B] AS `Outlet B Total`
FROM (
    SELECT Date, Outletname, Total FROM table1
) AS SourceTable
PIVOT (
    SUM(Total)
    FOR Outletname IN ([Outlet A], [Outlet B])
) AS PivotTable
ORDER BY Date;

PostgreSQL #

需先启用tablefunc扩展，再使用crosstab函数实现：

-- 仅需执行一次：启用扩展
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- 创建视图
CREATE VIEW view1 AS
SELECT
    date,
    "Outlet A" AS "Outlet A Total",
    "Outlet B" AS "Outlet B Total"
FROM crosstab(
    'SELECT Date, Outletname, Total FROM table1 ORDER BY 1,2',
    'SELECT DISTINCT Outletname FROM table1 ORDER BY 1'
) AS ct(date DATE, "Outlet A" INT, "Outlet B" INT)
ORDER BY date;

内容的提问来源于stack exchange，提问作者Test777