192.168.1.1     21
192.168.1.1     23
192.168.1.1     80
192.168.1.2     21
192.168.1.2     80
192.168.1.3     23


IP              21         23        80   
192.168.1.1      1          1         1
192.168.1.2      1          0         1
192.168.1.3      0          1         0


select ip,sum(if(portid='21',1,0)) AS ftp, sum(if(portid='23',1,0)) as telnet, sum(if(portid='80',1,0)) as http, sum(if(portid='443',1,0)) as https, sum(if(portid='25',1,0)) as smtp, sum(if(portid='1521',1,0)) as oracle, sum(if(portid='445' or portid ='139',1,0)) as windows from fulltcpnmapports group by ip order by inet_aton(ip);