How can multiple rows be concatenated into one in Oracle without creating a stored procedure

Question

How can I achieve the following in oracle without creating a stored procedure   Data Set   question id    element id 1              7 1              8 2              9 3              10 3              11 3              12   Desired Result   question id    element id 1              7 8 2              9 3              10 11 12

User · Answer

Easy   SELECT question id  wm concat element id  as elements FROM   questions GROUP BY question id    Pesonally tested on 10g  -   From http   www oracle-base com articles 10g StringAggregationTechniques php

User · Answer

From Oracle 11gR2  the LISTAGG clause should do the trick   SELECT question id         LISTAGG element id       WITHIN GROUP  ORDER BY element id  FROM YOUR TABLE GROUP BY question id    Beware if the resulting string is too big  more than 4000 chars for a VARCHAR2  for instance   from version 12cR2  we can use ON OVERFLOW TRUNCATE ERROR to deal with this issue

User · Answer

This OTN-thread contains several ways to do string aggregation  including a performance comparison  http   forums oracle com forums message jspa messageID 1819487 1819487

User · Answer

There are many way to do the string aggregation  but the easiest is a user defined function   Try this for a way that does not require a function   As a note  there is no simple way without the function   This is the shortest route without a custom function   it uses the ROW NUMBER   and SYS CONNECT BY PATH functions    SELECT questionid         LTRIM MAX SYS CONNECT BY PATH elementid              KEEP  DENSE RANK LAST ORDER BY curr       AS elements FROM    SELECT questionid                 elementid                 ROW NUMBER   OVER  PARTITION BY questionid ORDER BY elementid  AS curr                 ROW NUMBER   OVER  PARTITION BY questionid ORDER BY elementid  -1 AS prev         FROM   emp  GROUP BY questionid CONNECT BY prev   PRIOR curr AND questionid   PRIOR questionid START WITH curr   1

[sql] How can multiple rows be concatenated into one in Oracle without creating a stored procedure?

Examples related to sql

Examples related to oracle

Examples related to concatenation

Examples related to string-aggregation