KQL query for pivot output

Question

Hi, I'm new to KQL. I need to pivot the data in this detail data on multiple columns into a pivot format :

Detail Table:

Pivot Table:

Answer 1

Hi @Raj Bhat ,

Thank you for posting query in Microsoft Q&A Platform.

Kindly use below query. I tried its working fine.

//created a table with sample data.  
  
.create table SampleTable(  
Name:string, OSStatus:string, DBLevel:string, DBStatus:string)  
  
// inserting sample data in to table  
.ingest inline into table SampleTable <|  
ABC,OSStatus-Completed,Hourly,Fail  
ABC,Completed,Incr,Fail  
XYZ,Completed,Incr,Success  
XYZ,Completed,Hourly,Success  
  
//Actual query for pivoting table  
let OSStatusPivot = SampleTable  
| evaluate pivot(OSStatus, count(), Name):(Name:string, Completed:string, Failed:string )  
| project Name, ["OSStatus-Completed"]=Completed, ["OSStatus-Failed"]=Failed;  
let DBStatusPivot = SampleTable  
| project Name, DBStatus=strcat('DBStatus-',DBLevel, '-' ,DBStatus)  
| evaluate pivot(DBStatus, count()):  
        (Name:string ,   
        ["DBStatus-Hourly-Fail"]:string,  
        ["DBStatus-Hourly-Success"]:string,  
        ["DBStatus-Incr-Fail"]:string,  
        ["DBStatus-Incr-Success"]:string,  
        ["DBStatus-Full-Success"]:string,  
        ["DBStatus-Full-Fail"]:string);  
OSStatusPivot | join  DBStatusPivot on Name  

Hope this helps.

------------

Please consider hitting Accept Answer and up-vote button. Accepted answers help community as well.

Answer 2

Hello, I personally don't know KQL however I did find this article that may help structure your query!

https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/pivotplugin

Hopefully someone with more experience can provide a actual example of what you're looking for! :)