Pivoting in KQL

Bexy Morgan 255 Reputation points
2023-07-24T11:28:50.55+00:00

As There are 2 tables. Input table is t1 and output query table should like like output_table

.create table t1 ( Timestamp:datetime, From:string, TravelMode:string, Tier:string)
 .ingest inline into table t1 <|  
2010-01-01,NY,Flight,Premium
2010-01-02,Boston,Train,Economy
2010-01-02,NY,Flight,Business
2010-01-02,Boston,Bus,Business
2010-01-03,Philadelphia,Train,Premium
2010-01-04,Texas,Bus,Economy
2010-01-05,Texas,Bus,Economy
2010-01-05,NJ,Train,Business
2010-01-06,NJ,Bus,Premium
2010-01-07,NJ,Bus,Economy
2010-01-08,NJ,Flight,Premium
2010-01-08,Newark,Bus,Business
2010-01-08,Newark,Flight,Economy
2010-01-08,Michigan,Bus,Economy
2010-01-09,Michigan,Train,Economy
2010-01-10,NY,Bus,Business
2010-01-11,NY,Flight,Economy
2010-01-12,NY,Train,Premium
2010-01-13,Birmingham,Bus,Business
2010-01-14,NY,Train,Business
2010-01-07,NY,Bus,Premium
2010-01-07,NY,Train,Economy
2010-01-07,NY,Flight,Economy
2010-01-08,NY,Train,Premium 
2010-01-18,Ohio,Flight,Premium
2010-01-18,Ohio,Flight,Premium
2010-01-18,Ohio,Flight,Business
2010-01-18,Ohio,Flight,Economy
2010-01-18,Ohio,Flight,Economy
2010-01-19,Ohio,Bus,Premium
2010-01-19,Ohio,Bus,Premium
///////////////////////////////////////////////////////////////////////////////////////////////////////////////
.create table output_table ( Timestamp:datetime, From:string, Flight:int, Train:int, Bus:int, Business:int, Premium: int ,Economy: int)
 .ingest inline into table output_table <|
2010-01-01,NY,1,0,0,1,0,0
2010-01-02,Boston,0,1,1,0,1,1
2010-01-02,NY,1,0,0,0,0,1
2010-01-03,Philadelphia,0,1,0,1,0,0
2010-01-04,Texas,0,0,1,0,1,0
2010-01-05,Texas,0,0,1,0,1,0
2010-01-05,NJ,0,1,0,0,0,1
2010-01-06,NJ,0,0,1,1,0,0
2010-01-07,NJ,0,0,1,0,1,0
2010-01-08,NJ,1,0,0,1,0,0
2010-01-08,Newark,1,0,1,0,1,1
2010-01-08,Michigan,0,1,0,0,1,0
2010-01-09,Michigan,0,1,0,0,1,0
2010-01-10,NY,0,0,1,0,0,1
2010-01-11,NY,1,0,0,0,1,0
2010-01-12,NY,0,1,0,1,0,0
2010-01-18,Ohio,5,0,0,2,2,1
2010-01-19,Ohio,0,0,2,2,0,0
Azure Data Explorer
Azure Data Explorer
An Azure data analytics service for real-time analysis on large volumes of data streaming from sources including applications, websites, and internet of things devices.
459 questions
{count} votes

Accepted answer
  1. Wilko van de Velde 2,146 Reputation points
    2023-07-25T06:21:53.01+00:00

    I think this is the answer to your solution:

    t1
    | evaluate pivot(TravelMode)
    | evaluate pivot(Tier)
    | summarize 
        Bus = sum(Bus),
        Flight = sum(Flight),
        Train = sum(Train),
        Business = sum(Business),
        Economy = sum(Economy),
        Premium = sum(Premium)
        by Timestamp, From
    

    You can change the column order by using project.

    Small note, when I executed the query below, I got 2 different Premium columns. Probably caused by a typo.

    t1
    | evaluate pivot(TravelMode)
    | evaluate pivot(Tier)
    

    If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful