0

Hello i am trying to figure out how i can make a query to get the following information into a set from a table.

tbl_hosts
============
ip           proto       port
1.1.1.1        tcp        123
1.1.1.1        tcp        161
1.1.1.1        tcp        443
1.1.1.1        udp        161
1.1.1.1        udp        123
1.1.1.2        tcp        80
1.1.1.3        tcp        80
1.1.1.3        tcp        443

I am trying to figure out how i can get the following information in this format

1.1.1.1 - tcp - 123,161,443
1.1.1.2 - tcp - 80
1.1.1.3 - tcp - 80,443

Thank you very much for your response Gordon Linoff, just had one more question, would it be possible to also do something like this, in case there were more than one protocols with multiple ports for same ip's

1.1.1.1 - (tcp:123,161,443)(udp:161,123)
1.1.1.2 - tcp - 80
1.1.1.3 - tcp - 80,443
Mr39
  • 51
  • 1
  • 10
  • 2
    Possible duplicate of [Postgresql GROUP\_CONCAT equivalent?](https://stackoverflow.com/questions/2560946/postgresql-group-concat-equivalent) – PM 77-1 Apr 18 '19 at 17:29

1 Answers1

2

I recommend array_agg():

select ip, proto, array_agg(port) as ports
from t
group by ip, proto;

Arrays are usually easier to work with in Postgres than strings. However, if you really need a string, use string_agg() instead.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you very much for you response, i just tried the following query select ip, string_agg(protocol,port) as ports from t group by ip but the output will come out weird, like since the tcp for 1.1.1.1 only has 3 ports, but it will show in the output like 6 times and the udp which only has 2 ports will show 4 times, which is really weird, not sure why that is happening. – Mr39 Apr 18 '19 at 18:13