求助：如何将SQL查询结果行转列（Pivot）生成指定格式报表

当前查询及返回结果 #

我现在用这条SQL统计预约状态的日数据：

select count(*) as count, appointment_status
    , cast(created_time as date) as date
from appointment_master
where created_time >= '2024-01-21'
group by appointment_status, cast(created_time as date)
order by CAST(created_time as date)

返回的结果是：

期望输出格式 #

想把行转成列，得到这样的结果：

Date      Arrived   Prescribed
    22/01/2024    3          11
    23/01/2024    4          13
    24/01/2024    3          10

表数据示例 #

源表appointment_master的部分数据如下：

可行的SQL解决方案 #

方法1：使用CASE WHEN + 聚合函数（通用所有SQL数据库） #

这是兼容性最强的写法，适配MySQL、PostgreSQL、SQL Server等绝大多数数据库：

select
    cast(created_time as date) as Date,
    sum(case when appointment_status = 'Arrived' then 1 else 0 end) as Arrived,
    sum(case when appointment_status = 'Prescribed' then 1 else 0 end) as Prescribed
from appointment_master
where created_time >= '2024-01-21'
group by cast(created_time as date)
order by cast(created_time as date);

核心逻辑：按日期分组，用CASE WHEN判断每条记录的状态，符合目标状态则计1，否则计0，最后用SUM汇总每个日期下各状态的总数。

方法2：使用PIVOT函数（适用于SQL Server、Oracle等支持的数据库） #

如果你的数据库支持PIVOT语法，可以用更简洁的写法：

select
    Date,
    Arrived,
    Prescribed
from (
    select
        cast(created_time as date) as Date,
        appointment_status
    from appointment_master
    where created_time >= '2024-01-21'
) as src
pivot (
    count(appointment_status)
    for appointment_status in (Arrived, Prescribed)
) as pvt
order by Date;

核心逻辑：子查询先提取日期和状态字段，再用PIVOT将状态值转成表头列，同时完成计数聚合。

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