Pivoting in KQL

Question

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

Answer 1

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".