select date1,; sum(iif(no = 1, no, $0)) as truck,; sum(iif(no = 2, no, $0)) as trolly,; sum(iif(no = 3, no, $0)) as troller,; sum(iif(no = 4, no, $0)) as ttroler,; sum(iif(no = 5, no, $0)) as tanker,; sum(iif(no = 6, no, $0)) as bcart,; sum(iif(no = 7, no, $0)) as ccart,; sum(iif(no = 8, no, $0)) as dcart,; sum(iif(no = 9, no, $0)) as other,; count(no) as veh,; from table1; group by 1; into table tmp