Snowflake中筛选透视表仅保留含数据列的技术方案问询

问题背景 #

我们有一套问卷调研软件，每份问卷问题数量不固定（通常1-30个，无上限），数据已透视为宽表格式：每行对应一条问卷响应，包含Survey ID、Response_ID及各类问题列（如Question1、Q1等），非当前问卷的问题列值均为NULL。需求是在BI工具中筛选不同Survey ID时，仅展示该问卷有数据的列，同时保证每个Response_ID对应一行记录。

当前尝试的SQL（select survey_ID, Response_ID, max(*) ...）存在语法错误，且无法动态适配不同问卷的列结构。

核心解决思路 #

需求需分两步实现：先聚合数据确保每个响应仅一行，再通过工具或SQL逻辑动态隐藏空列。

方案一：结合BI工具列隐藏功能（推荐） #

主流BI工具（Tableau、Power BI、FineBI等）均支持基于筛选条件的列显示/隐藏配置，步骤如下：

1. 先完成数据聚合，提取每个响应的有效数据：

SELECT
  Survey_ID,
  Response_ID,
  MAX(Question1) AS Question1,
  MAX(Question2) AS Question2,
  MAX(Q1) AS Q1,
  MAX(Q2) AS Q2
  -- 按实际情况补充所有可能的问题列
FROM surveypivot
GROUP BY Survey_ID, Response_ID

（注：使用MAX()是因为每个响应在对应问卷的问题列仅存在一个非NULL值，聚合后可精准提取该值）
2. 在BI工具中设置列显示规则：

• Question1/Question2：仅当Survey_ID为ABC或GHI时显示
• Q1：仅当Survey_ID为DEF时显示
• 其他问题列同理，根据对应问卷ID配置显示条件

筛选不同问卷时，工具会自动隐藏无数据的列，满足需求。

方案二：动态SQL实现（纯SQL场景适用） #

若需直接通过SQL返回动态列，可使用动态SQL生成对应问卷的查询语句（以MySQL为例）：

SET @survey_id = 'DEF'; -- 替换为目标问卷ID

-- 生成当前问卷需查询的列列表
SELECT GROUP_CONCAT(DISTINCT COLUMN_NAME) INTO @cols
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'surveypivot'
AND (COLUMN_NAME IN ('Survey_ID', 'Response_ID')
     OR EXISTS (SELECT 1 FROM surveypivot WHERE Survey_ID = @survey_id AND COLUMN_NAME IS NOT NULL));

-- 拼接并执行动态SQL
SET @sql = CONCAT('SELECT ', @cols, ' FROM surveypivot WHERE Survey_ID = ''', @survey_id, ''' GROUP BY Survey_ID, Response_ID');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

注意：不同数据库动态SQL语法有差异（如SQL Server用EXEC sp_executesql，Oracle用EXECUTE IMMEDIATE），需根据实际环境调整，同时要对输入的survey_id做合法性校验，避免SQL注入风险。

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