在T-SQL（Azure SQL数据仓库）中动态生成JSON列的问题

你的代码返回全NULL的核心原因是字符串拼接时只要有一个部分为NULL，整个结果就会变成NULL（比如当cat_qty和dog_qty都为0时，两个CASE都返回NULL，导致整个拼接串失效）。另外还有几个细节错误：

• COALESCE(age,'Unknown') 类型不匹配：age是数值类型，'Unknown'是字符串，必须先转成VARCHAR才能拼接
• COALESCE(red,'Unknown') 是笔误，应该是COALESCE(fav_colour,'Unknown')
• 拼接后可能出现末尾多余的逗号，导致JSON格式非法

修复后的手动拼接方案 #

先把每个可能返回NULL的部分用COALESCE(..., '')转成空字符串，处理类型匹配后，再去掉末尾多余的逗号：

SELECT 
  name,
  -- 移除末尾多余逗号后闭合大括号
  '{' + LEFT(json_parts, LEN(json_parts) - 1) + '}' AS json_row
FROM (
  SELECT 
    name,
    -- 每个片段都用COALESCE确保不会返回NULL
    COALESCE('"birthday": "' + CAST(birthday AS VARCHAR(10)) + '",', '') +
    COALESCE('"age": ' + CAST(COALESCE(age, 'Unknown') AS VARCHAR(10)) + ',', '') +
    COALESCE('"fav_colour": "' + COALESCE(fav_colour, 'Unknown') + '",', '') +
    COALESCE(CASE WHEN SUM(cat_qty) IS NOT NULL AND SUM(cat_qty) <> 0 THEN '"cats_owned": ' + CAST(SUM(cat_qty) AS VARCHAR(10)) + ',' END, '') +
    COALESCE(CASE WHEN SUM(dog_qty) IS NOT NULL AND SUM(dog_qty) <> 0 THEN '"dogs_owned": ' + CAST(SUM(dog_qty) AS VARCHAR(10)) + ',' END, '') AS json_parts
  FROM YourTable -- 注意：不要用关键字Table当表名，替换为实际表名
  GROUP BY name, birthday, age, fav_colour
) t

更优方案：用FOR JSON PATH自动生成（推荐） #

手动拼接JSON容易出错，Azure SQL Data Warehouse支持FOR JSON PATH，可以自动处理格式，还能通过条件判断自动排除不需要的键：

SELECT 
  name,
  (
    SELECT 
      COALESCE(CONVERT(VARCHAR(10), birthday, 23), 'Unknown') AS birthday,
      COALESCE(CAST(age AS VARCHAR(10)), 'Unknown') AS age,
      COALESCE(fav_colour, 'Unknown') AS fav_colour,
      CASE WHEN SUM(cat_qty) IS NOT NULL AND SUM(cat_qty) <> 0 THEN SUM(cat_qty) END AS cats_owned,
      CASE WHEN SUM(dog_qty) IS NOT NULL AND SUM(dog_qty) <> 0 THEN SUM(dog_qty) END AS dogs_owned
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ) AS json_row
FROM YourTable
GROUP BY name, birthday, age, fav_colour

这个方案的优势：

• 自动处理JSON格式，无需手动加引号和逗号，避免格式错误
• 当CASE返回NULL时，对应的键会被自动排除，正好满足你“值为0或NULL时不显示键”的需求
• 代码更简洁易维护

关键注意事项 #

1. 不要用Table作为表名，这是SQL关键字，会导致语法错误
2. CONVERT(VARCHAR(10), birthday, 23) 是把日期转成标准的YYYY-MM-DD格式，符合JSON的日期表示习惯
3. WITHOUT_ARRAY_WRAPPER 用来去掉默认的数组包裹，直接返回单个JSON对象

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