如何在KUSTO中多表关联并将值转换为列展示？

你需要的是**行转列（透视）**操作，union只是将多个表的行合并，无法直接生成宽表。可以通过union合并所有数据源后，配合summarize和pivot来实现需求：

let table1 = datatable(companyCode:string, status:string, environment:string) 
[
    "company1", "Healthy", "DEV",
    "company2", "Healthy", "DEV", 
    "company3", "Healthy", "DEV",
];
let table2 = datatable(companyCode:string, status:string, environment:string) 
[
    "company1", "Healthy", "TEST",
    "company2", "Healthy", "TEST", 
    "company4", "Healthy", "TEST",
];
let table3 = datatable(companyCode:string, status:string, environment:string) 
[
    "company1", "Healthy", "UAT",
    "company2", "Healthy", "UAT", 
    "company4", "Healthy", "UAT",
    "company5", "Healthy", "UAT",
];
// 合并所有表的数据
union table1, table2, table3
// 按公司分组，将环境字段转为列名，状态作为对应列的值
| summarize pivot(environment, any(status)) by companyCode

关键逻辑说明 #

• union table1, table2, table3：把三个环境的表合并成一个包含所有公司-环境-状态的长表
• summarize pivot(environment, any(status)) by companyCode：
  • by companyCode：按公司代码分组聚合
  • pivot(environment, any(status))：将environment的不同取值（DEV/TEST/UAT）转为列名，用any(status)提取每个公司对应环境的状态值（单个公司在单个环境下只有一条记录，any()可直接获取对应状态）

执行后就能得到你想要的宽表格式。

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