Make Concatenation with different number of columns in Sequence

Question

I have the folowing query

select dated,sum(substr(hrs,1,2)) ||':'|| sum(substr(hrs,4,5)) hrs,dpt,reason
from dpr.stp_00_00
group by dated,dpt,reason
order by dated

Returning:

DATED       HRS     DPT         REASON
10/14/2011  2:5     Mechanical  Boiler tubes damaged & less availability of steam 
10/14/2011  0:20    Mechanical  Breakage of link of bagasse carrier # 1 and surplus 
10/14/2011  1:0     Mechanical  Choke at belt conveyor 
10/15/2011  0:10    Mechanical  Boiler pressure dropped
10/16/2011  1:30    Electrical  Power failure / 505 governor card damaged of 
10/16/2011  0:10    Mechanical  3rd mill taken in line
10/16/2011  0:30    Mechanical  Raw juice line before primary heaters busted
10/16/2011  1:0     Mechanical  Raw juice pump at mill house NRV body got busted

Problem in this query is that i want to show all reason in each date in single row so that all reason should be shown in single row against single date For example for the 14-OCT-2012 there are three reasons with same dept and same date i want to group also the reason so that it will shown in single row like

Dated         HRS  DPT           Reason
10/14/2012    2:5  Mechanical    All Resons of all 14 oct

Anyone please tell me how can i do this using sql query or any user defined function

Which version of Oracle? How do you want the reasons separated - with a comma, semi-colon, etc. - and how should they be ordered in the list? Do the hours need to be summed too? I assume that would be 3:25 in your example? — Alex Poole, Jan 27 '12 at 11:44
Do you have to do this in just one query? This can become a lot easier if you get the data first and process it afterwards using a Stored Procedure for example... — Felipe, Jan 27 '12 at 11:47
@Alex Poole I am using Oracle 9i and i want comma separated reason column — user1103342, Jan 27 '12 at 12:00
@Komyg Yes Whatever u can do it I accept it as well but please tell how can i so this — user1103342, Jan 27 '12 at 12:00
possible duplicate of [Is there an Oracle SQL query that aggregates multiple rows into one row?](http://stackoverflow.com/questions/1120706/is-there-an-oracle-sql-query-that-aggregates-multiple-rows-into-one-row) — APC, Jan 27 '12 at 12:05

score 3 · Answer 1 · edited May 23 '17 at 10:34

3

You can find a similar question, and offered answers on the topic, here.

Additionally, Tom Kyte has written a function called stragg to do this - you can find that here, although he has subsequently offered an alternative approach here.

edited May 23 '17 at 10:34

Community

1
1

answered Jan 27 '12 at 11:51

score 0 · Accepted Answer · answered Jan 28 '12 at 04:23

Please Try this

CREATE OR REPLACE FUNCTION concatenate_list (p_cursor IN  SYS_REFCURSOR)
  RETURN  VARCHAR2
IS
  l_return  VARCHAR2(32767);
  l_temp    VARCHAR2(32767);
BEGIN
  LOOP
    FETCH p_cursor
    INTO  l_temp;
--    EXIT WHEN p_cursor%NOTFOUND;
    l_return := l_return ||   '  ,' || l_temp;
  END LOOP;
  RETURN LTRIM(l_return, ',');
END;
/


SELECT dated,dpt,
       concatenate_list(CURSOR(SELECT e2.reason FROM dpr.stp_00_00 e2 WHERE e2.dated = e1.dated and e1.dpt=e2.dpt)) Reasons
FROM   (SELECT DISTINCT dpt,dated
        FROM dpr.stp_00_00 order by dated) e1;

Hope this will be helpful

Make Concatenation with different number of columns in Sequence

2 Answers2