Kusto table transformation

Question

i have following table in Kusto language:

let my_data = datatable(Environment: string, Service:string, Url:string, Release: string)
[
    "dev", "Service1", "Url1", "Release1",
    "uat", "Service1", "Url2", "Release2",
    "dev", "Service2", "Url3", "Release3",
    "uat", "Service2", "Url4", "Release4",
    "dev", "Service3", "Url5", "Release5",
    "uat", "Service3", "Url6", "Release6",
];

And would like transform it to:

let transformed = datatable(Service: string, dev_url: string, dev_release: string, uat_url: string, uat_release: string)
[
    "Service1", "Url1", "Release1", "Url2", "Release2",
    "Service2", "Url3", "Release3", "Url4", "Release4",
    "Service3", "Url5", "Release5", "Url6", "Release6"
];

But by any chance i can't figure it out. Does anyone can help? :)

Answer 1

You can use join with filters

let my_data = datatable(Environment: string, Service:string, Url:string, Release: string)
[
    "dev", "Service1", "Url1", "Release1",
    "uat", "Service1", "Url2", "Release2",
    "dev", "Service2", "Url3", "Release3",
    "uat", "Service2", "Url4", "Release4",
    "dev", "Service3", "Url5", "Release5",
    "uat", "Service3", "Url6", "Release6",
];
my_data| where Environment=='dev'
| join (my_data | where Environment=='uat') on Service
| project Service, dev_url=Url, dev_release=Release, uat_url=Url1, uat_realase=Release1

UPDATE I found the way to do it with dynamic Environment

let my_data = datatable(Environment: string, Service:string, Url:string, Release: string)
[
    "dev", "Service1", "Url1", "Release1",
    "uat", "Service1", "Url2", "Release2",
    "xyz", "Service1", "Url3", "Release2",
    "dev", "Service2", "Url3", "Release3",
    "uat", "Service2", "Url4", "Release4",
    "dev", "Service3", "Url5", "Release5",
    "uat", "Service3", "Url6", "Release6",
];
let relases = my_data
| summarize d = make_bag(pack(strcat(Environment,"_release"), Release)) by Service
| evaluate bag_unpack(d);
let urls = my_data
| summarize d = make_bag(pack(strcat(Environment,"_url"), Url)) by Service
| evaluate bag_unpack(d);
relases
| join urls on Service
| project-away Service1
| project-reorder Service , * asc