Grafana实现SQL Server多表关联透视表（按描述、日期分组）求助

表结构 #

• SAMPLES (id, sample_number, sample_date, description)
• PARAMETERS (id, parameter_name, measure_unit)
• RESULTS (id, sample_id 外键, parameter_id 外键, value)

需求 #

需要在Grafana中关联三个表生成表格，先按description分组，再按sample_date分组，实现与现有Excel表格一致的展示效果。

尝试的查询及问题 #

使用以下动态PIVOT查询后未得到预期结果，不清楚如何修改查询或配置Grafana Transformations：

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);

SELECT 
    @cols = STRING_AGG(QUOTENAME(parameter_name), ', ')  
FROM 
    PARAMETERS;

SET @query = '
SELECT 
    description AS "Description",
    sample_date AS "Date", ' + @cols + '
FROM 
    (SELECT S.description, S.sample_date, P.parameter_name, R.value
     FROM SAMPLES S
     JOIN RESULTS R ON S.id = R.sample_id
     JOIN PARAMETERS P ON R.parameter_id = P.id) AS SourceTable
PIVOT
    (MAX(value) 
     FOR parameter_name IN (' + @cols + ')
    ) AS PivotTable
ORDER BY 
    description, sample_date;';

EXEC sp_executesql @query;

解决方案 #

1. 优化SQL查询 #

处理空值显示

原查询中无对应结果的参数列会显示NULL，可通过ISNULL替换为友好文本，同时统一日期格式避免Grafana展示异常：

DECLARE @cols AS NVARCHAR(MAX), @cols_with_isnull AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);

-- 生成带空值处理的列定义
SELECT 
    @cols = STRING_AGG(QUOTENAME(parameter_name), ', '),
    @cols_with_isnull = STRING_AGG(CONCAT('ISNULL(', QUOTENAME(parameter_name), ', ''无数据'') AS ', QUOTENAME(parameter_name)), ', ')
FROM 
    PARAMETERS;

SET @query = '
SELECT 
    description AS "Description",
    CONVERT(VARCHAR(10), sample_date, 23) AS "Date", ' + @cols_with_isnull + '
FROM 
    (SELECT S.description, S.sample_date, P.parameter_name, R.value
     FROM SAMPLES S
     JOIN RESULTS R ON S.id = R.sample_id
     JOIN PARAMETERS P ON R.parameter_id = P.id) AS SourceTable
PIVOT
    (MAX(value) 
     FOR parameter_name IN (' + @cols + ')
    ) AS PivotTable
ORDER BY 
    description, sample_date;';

EXEC sp_executesql @query;

处理重复数据

如果同一个description+sample_date+parameter_name组合存在多条结果，需根据业务逻辑调整聚合方式：

• 取最新值：在SourceTable中添加ROW_NUMBER()分区排序，筛选最新记录
• 取平均值/总和：将PIVOT中的MAX(value)替换为AVG(value)或SUM(value)

2. Grafana Transformations配置 #

若SQL返回结果已符合行列结构，可通过Grafana转换功能对齐Excel格式：

• 分组调整：添加「Group by」转换，依次选择Description、Date作为分组键，参数列选择First/Last聚合
• 列名重命名：用「Manual rename」调整列名，与Excel保持一致
• 排序优化：添加「Sort」转换，按Description升序、Date升序排序

常见问题排查 #

• 参数列缺失：确认PARAMETERS表包含所有需展示的参数，且SQL Server版本为2017+（支持STRING_AGG，低版本需用STUFF+FOR XML PATH替代）
• 重复行：检查是否存在重复的样本-参数组合，需在SourceTable中先去重或调整聚合逻辑

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