Append value of a column in Oracle

Question

I have a column in my table which saves IP addresses. I do not know how many IP are present in that column and my DBA would not help me with spool to select * from table because of permissions.

Is there a way I could append the column value?

ID   --   Name   --   IP
1         ABC        192.168.00.00, 192.168.00.11,...

I would like to append to the IP's by comma separating like below

ID   --   Name   --   IP
1         ABC        192.168.00.00, 192.168.00.11,..., 192.168.00.22

how is the `ip` stored? Is it already comma separated? If no then please give the structure of the table. And if your DBA is not letting you run `select * from` query, then how are you hoping to run the query which we would provide. — Utsav, Jan 12 '16 at 09:20

Lee · Accepted Answer · 2016-01-12T09:23:13.567

Not 100% sure what you mean, but I'm guessing your column IP is storing a single IP address and you want to do a "String aggregation" to group all IPs for the same ID and name to be one string.

Try this

SELECT ID, Name, wm_concat(IP) AS IPs
FROM   table
GROUP BY ID, Name;

OR

SELECT ID, Name, LISTAGG(IP, ',') WITHIN GROUP (ORDER BY IP) AS IPs
FROM   table
GROUP BY ID, Name;

There're other alternatives too here in this article.

score 1 · Answer 2 · edited May 23 '17 at 12:13

You could do it using LISTAGG.

For example,

SQL> SELECT deptno id,
  2    listagg(ename, ',') WITHIN GROUP (
  3  ORDER BY empno) NAME
  4  FROM emp
  5  GROUP BY deptno;

        ID NAME
---------- ------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

SQL>

But remember, LISTAGG has a SQL limit of 4000 characters/bytes. If your string aggregation goes beyond 4000 characters than you would receive ORA-01489: result of string concatenation is too long. You could use XMLAGG as an alternative. See https://stackoverflow.com/a/29776515/3989608

Append value of a column in Oracle

2 Answers2