Snowflake中如何将Pivot结果的0/1转为布尔值（无需CTE）

原始stores表数据 #

STORE_NO    STORE_ADDRESS   STORE_TYPE  STORE_OWNER STORE_HOURS
1           123             Drive Thru  Harpo       24hrs
1           123             Curbside    Harpo       24hrs
1           123             Counter     Harpo       24hrs
2           456             Drive Thru  Groucho     9 to 9
2           456             Counter     Groucho     9 to 9

目标透视表格式 #

STORE_NO    STORE_ADDRESS   Drive Thru  Curbside    Counter STORE_OWNER STORE_HOURS
1           123             TRUE        TRUE        TRUE    Harpo       24hrs
2           456             TRUE        FALSE       TRUE    Groucho     9 to 9

当前问题 #

你目前使用的透视SQL语句返回0/1数值，需要将其转换为TRUE/FALSE布尔值，且要求不使用CTE。

解决方案 #

通用方案（适配PostgreSQL、SQL Server等支持BOOLEAN类型的数据库） #

直接在透视结果外层，将计数的0/1值CAST为BOOLEAN类型——数据库会自动将0转为FALSE，非0值转为TRUE：

SELECT 
  STORE_NO,
  STORE_ADDRESS,
  CAST("Drive Thru" AS BOOLEAN) AS "Drive Thru",
  CAST("Curbside" AS BOOLEAN) AS "Curbside",
  CAST("Counter" AS BOOLEAN) AS "Counter",
  STORE_OWNER,
  STORE_HOURS
FROM stores
PIVOT (
  COUNT(STORE_TYPE) 
  FOR STORE_TYPE IN ('Drive Thru', 'Curbside', 'Counter')
) AS store_flattened;

MySQL 适配方案 #

MySQL没有原生BOOLEAN类型（实际是TINYINT(1)的别名），可以用IF函数将0/1映射为'TRUE'/'FALSE'文本：

SELECT 
  STORE_NO,
  STORE_ADDRESS,
  IF(`Drive Thru` = 1, 'TRUE', 'FALSE') AS `Drive Thru`,
  IF(`Curbside` = 1, 'TRUE', 'FALSE') AS `Curbside`,
  IF(`Counter` = 1, 'TRUE', 'FALSE') AS `Counter`,
  STORE_OWNER,
  STORE_HOURS
FROM stores
PIVOT (
  COUNT(STORE_TYPE) 
  FOR STORE_TYPE IN ('Drive Thru', 'Curbside', 'Counter')
) AS store_flattened;

无需透视的替代写法（可选） #

如果不想用PIVOT语法，也可以用MAX(CASE)组合直接生成布尔结果：

SELECT 
  STORE_NO,
  STORE_ADDRESS,
  MAX(CASE WHEN STORE_TYPE = 'Drive Thru' THEN TRUE ELSE FALSE END) AS "Drive Thru",
  MAX(CASE WHEN STORE_TYPE = 'Curbside' THEN TRUE ELSE FALSE END) AS "Curbside",
  MAX(CASE WHEN STORE_TYPE = 'Counter' THEN TRUE ELSE FALSE END) AS "Counter",
  STORE_OWNER,
  STORE_HOURS
FROM stores
GROUP BY STORE_NO, STORE_ADDRESS, STORE_OWNER, STORE_HOURS;

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