SQL Query to concatenate column values from multiple rows in Oracle

Question

Would it be possible to construct SQL to concatenate column values from multiple rows    The following is an example    Table A   PID A B C   Table B   PID   SEQ    Desc  A     1      Have A     2      a nice A     3      day  B     1      Nice Work  C     1      Yes C     2      we can  C     3      do  C     4      this work    Output of the SQL should be -   PID   Desc A     Have a nice day  B     Nice Work  C     Yes we can do this work    So basically the Desc column for out put table is a concatenation of the SEQ values from Table B   Any help with the SQL

User · Answer

Before you run a select query  run this    SET SERVEROUT ON SIZE 6000    SELECT XMLAGG XMLELEMENT E SUPLR SUPLR ID        EXTRACT    text      SUPPLIER   FROM SUPPLIERS

User · Answer

The LISTAGG analytic function was introduced in Oracle 11g Release 2  making it very easy to aggregate strings  If you are using 11g Release 2 you should use this function for string aggregation  Please refer below url for more information about string concatenation   http   www oracle-base com articles misc StringAggregationTechniques php  String Concatenation

User · Answer

There are a few ways depending on what version you have - see the oracle documentation on string aggregation techniques  A very common one is to use LISTAGG   SELECT pid  LISTAGG Desc       WITHIN GROUP  ORDER BY seq  AS description FROM B GROUP BY pid    Then join to A to pick out the pids you want   Note  Out of the box  LISTAGG only works correctly with VARCHAR2 columns

User · Answer

Try this code        SELECT XMLAGG XMLELEMENT E fieldname        EXTRACT    text      FieldNames      FROM FIELD MASTER     WHERE FIELD ID  gt  10 AND FIELD AREA     NEBRASKA

User · Answer

As most of the answers suggest  LISTAGG is the obvious option  However  one annoying aspect with LISTAGG is that if the total length of concatenated string exceeds 4000 characters  limit for VARCHAR2 in SQL    the below error is thrown  which is difficult to manage  in Oracle versions upto 12 1      ORA-01489  result of string concatenation is too long   A new feature added in 12cR2 is the ON OVERFLOW clause of LISTAGG  The query including this clause would look like   SELECT pid  LISTAGG Desc      on overflow truncate  WITHIN GROUP  ORDER BY seq  AS desc FROM B GROUP BY pid    The above will restrict the output to 4000 characters but will not throw the  ORA-01489 error   These are some of the additional options of ON OVERFLOW clause    ON OVERFLOW TRUNCATE  Contd        This will display  Contd    at the end of string  Default is       ON OVERFLOW TRUNCATE         This will display the 4000 characters without any terminating string  ON OVERFLOW TRUNCATE  WITH COUNT   This will display the total number of characters at the end after the terminating characters  Eg -      5512   ON OVERFLOW ERROR   If you expect the LISTAGG to fail with the ORA-01489 error   Which is default anyway

User · Answer

For those who must solve this problem using Oracle 9i  or earlier   you will probably need to use SYS CONNECT BY PATH  since LISTAGG is not available   To answer the OP  the following query will display the PID from Table A and concatenate all the DESC columns from Table B   SELECT pid  SUBSTR  MAX  SYS CONNECT BY PATH  description          3  all descriptions FROM          SELECT ROW NUMBER    OVER  PARTITION BY pid ORDER BY pid  seq  rnum  pid  description        FROM                 SELECT a pid  seq  description               FROM table a a  table b b               WHERE a pid   b pid                           START WITH rnum   1 CONNECT BY PRIOR rnum   rnum - 1 AND PRIOR pid   pid GROUP BY pid ORDER BY pid    There may also be instances where keys and values are all contained in one table  The following query can be used where there is no Table A  and only Table B exists   SELECT pid  SUBSTR  MAX  SYS CONNECT BY PATH  description          3  all descriptions FROM          SELECT ROW NUMBER    OVER  PARTITION BY pid ORDER BY pid  seq  rnum  pid  description        FROM                 SELECT pid  seq  description               FROM table b                        START WITH rnum   1 CONNECT BY PRIOR rnum   rnum - 1 AND PRIOR pid   pid GROUP BY pid ORDER BY pid    All values can be reordered as desired  Individual concatenated descriptions can be reordered in the PARTITION BY clause  and the list of PIDs can be reordered in the final ORDER BY clause     Alternately  there may be times when you want to concatenate all the values from an entire table into one row   The key idea here is using an artificial value for the group of descriptions to be concatenated   In the following query  the constant string  1  is used  but any value will work   SELECT SUBSTR  MAX  SYS CONNECT BY PATH  description          3  all descriptions FROM          SELECT ROW NUMBER    OVER  PARTITION BY unique id ORDER BY pid  seq  rnum  description        FROM                 SELECT  1  unique id  b pid  b seq  b description               FROM table b b                        START WITH rnum   1 CONNECT BY PRIOR rnum   rnum - 1    Individual concatenated descriptions can be reordered in the PARTITION BY clause   Several other answers on this page have also mentioned this extremely helpful reference  https   oracle-base com articles misc string-aggregation-techniques

User · Answer

With SQL model clause   SQL gt  select pid   2         ltrim sentence  sentence   3    from   select pid   4                  seq   5                  sentence   6             from b   7            model   8                  partition by  pid    9                  dimension by  seq   10                  measures  descr cast null as varchar2 100   as sentence   11                    sentence any  order by seq desc  12                      descr cv              sentence cv   1   13                     14            15   where seq   1  16     P SENTENCE - --------------------------------------------------------------------------- A Have a nice day B Nice Work  C Yes we can do this work   3 rows selected    I wrote about this here  And if you follow the link to the OTN-thread you will find some more  including a performance comparison

User · Answer

There s also an XMLAGG function  which works on versions prior to 11 2  Because WM CONCAT is undocumented and unsupported by Oracle  it s recommended not to use it in production system   With XMLAGG you can do the following   SELECT XMLAGG XMLELEMENT E ename        EXTRACT    text      Result   FROM employee names   What this does is   put the values of the ename column  concatenated with a comma  from the employee names table in an xml element  with tag E  extract the text of this aggregate the xml  concatenate it  call the resulting column  Result

User · Answer

In the select where you want your concatenation  call a SQL function   For example   select PID  dbo MyConcat PID     from TableA    Then for the SQL function   Function MyConcat  PID varchar 10   returns varchar 1000  as begin  declare  x varchar 1000    select  x   isnull  x        x   x         Desc   from TableB     where PID    PID   return  x   end   The Function Header syntax might be wrong  but the principle does work

User · Answer

LISTAGG delivers the best performance if sorting is a must 00 00 05 85    SELECT pid  LISTAGG Desc       WITHIN GROUP  ORDER BY seq  AS description FROM B GROUP BY pid  COLLECT delivers the best performance if sorting is not needed 00 00 02 90     SELECT pid  TO STRING CAST COLLECT Desc  AS varchar2 ntt   AS Vals FROM B GROUP BY pid   COLLECT with ordering is bit slower 00 00 07 08     SELECT pid  TO STRING CAST COLLECT Desc ORDER BY Desc  AS varchar2 ntt   AS Vals FROM B GROUP BY pid    All other techniques were slower

[sql] SQL Query to concatenate column values from multiple rows in Oracle

Examples related to sql

Examples related to oracle

Examples related to string-aggregation