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