5 Replies Latest reply: Mar 17, 2014 6:30 AM by AnnPricks E RSS

    All Data into One column

    Moazam Shareef

      Dear Guys,

      I'm working on time attendance system were i have to send weekly and month report to each employee, I have a non database column in forms where i want to fetch all the data based on the below query.

      SELECT  DISTINCT EMP_INFO.EMPID||'|'||EMP_INFO.EMP_NAME||'|'||QRYENTIREEVENTLIST.COMPANYNAME||'|'||
                                  QRYENTIREEVENTLIST.DEPARTNAME||'|'||
                                  MIN(QRYENTIREEVENTLIST.CARDEVENTTIME)||'|'||MAX(QRYENTIREEVENTLIST.CARDEVENTTIME)
                                  FROM QRYENTIREEVENTLIST,EMP_INFO WHERE EMPID=QRYENTIREEVENTLIST.PERSONID
                                  AND QRYENTIREEVENTLIST.CARDEVENTDATE BETWEEN :CTRL.FROM_DATE AND :CTRL.TO_DATE AND PERSONID=:CTRL.EMPID
                                  AND EMP_INFO.FSOCCP='No'
                                  GROUP BY EMPID,EMP_NAME,COMPANYNAME,DEPARTNAME,CARDEVENTDATE,
                                  REPORTING_MNGR,MNGR_EMAIL,EMP_EMAIL,ADMIN_MNGR_NAME,ADMIN_MNGR_MAIL ORDER BY PERSONID;
      

      how can i put the above query in a cursor and put all the bulk data into one column which has varchar2(4000) datatype, i'm using oracle 10g.

       

      Regards

        • 1. Re: All Data into One column
          Hoek

          Your question isn't entirely clear to me.

          Can you post a small, illustrative example/testcase?

          How to is explained here: Re: 2. How do I ask a question on the forums?

           

          If you need to aggregate data into one column, then these techniques might help you:

          ORACLE-BASE - String Aggregation Techniques

          • 2. Re: All Data into One column
            AnnPricks E

            Something like this?

            SELECT LISTAGG(str_val,',') WITHIN GROUP(ORDER BY personid) all_one_column

            FROM

            (SELECT  EMP_INFO.EMPID||'|'||EMP_INFO.EMP_NAME||'|'||QRYENTIREEVENTLIST.COMPANYNAME||'|'|| 

                    QRYENTIREEVENTLIST.DEPARTNAME||'|'|| 

                    MIN(QRYENTIREEVENTLIST.CARDEVENTTIME)||'|'||MAX(QRYENTIREEVENTLIST.CARDEVENTTIME) str_val

            FROM QRYENTIREEVENTLIST,EMP_INFO WHERE EMPID=QRYENTIREEVENTLIST.PERSONID 

            AND QRYENTIREEVENTLIST.CARDEVENTDATE BETWEEN :CTRL.FROM_DATE AND :CTRL.TO_DATE AND PERSONID=:CTRL.EMPID 

            AND EMP_INFO.FSOCCP='No' 

            GROUP BY EMPID,EMP_NAME,COMPANYNAME,DEPARTNAME,CARDEVENTDATE, 

            REPORTING_MNGR,MNGR_EMAIL,EMP_EMAIL,ADMIN_MNGR_NAME,ADMIN_MNGR_MAIL);

            • 3. Re: All Data into One column
              SKP

              Example:

               

              DECLARE

              CURSOR C1 IS

                SELECT emp.ename||emp.deptno||dept.deptno  str_agg FROM emp,dept WHERE emp.deptno=dept.deptno;

               

                TYPE C1_TYPE IS TABLE OF C1%ROWTYPE;

                REC C1_TYPE;

              BEGIN

               

              OPEN c1;

               

              loop

                FETCH C1 BULK COLLECT INTO REC1 LIMIT 10;

               

              FORALL I IN 1 .. REC1.COUNT

                  process(  REC(I));

                  COMMIT;

                  EXIT   WHEN C1%NOTFOUND;

              END LOOP;

              END;

              • 4. Re: All Data into One column
                Moazam Shareef

                Thanks for your replies.

                LISTAGG will not support version 10g.


                 

                • 5. Re: All Data into One column
                  AnnPricks E

                  Then use XMLAGG

                  SELECT SUBSTR(XMLAGG(XMLELEMNT(e,','||str_val).EXTRACT('//text()') ORDER BY personid),2) all_one_column

                  FROM

                  (SELECT  EMP_INFO.EMPID||'|'||EMP_INFO.EMP_NAME||'|'||QRYENTIREEVENTLIST.COMPANYNAME||'|'|| 

                          QRYENTIREEVENTLIST.DEPARTNAME||'|'|| 

                          MIN(QRYENTIREEVENTLIST.CARDEVENTTIME)||'|'||MAX(QRYENTIREEVENTLIST.CARDEVENTTIME) str_val

                  FROM QRYENTIREEVENTLIST,EMP_INFO WHERE EMPID=QRYENTIREEVENTLIST.PERSONID 

                  AND QRYENTIREEVENTLIST.CARDEVENTDATE BETWEEN :CTRL.FROM_DATE AND :CTRL.TO_DATE AND PERSONID=:CTRL.EMPID 

                  AND EMP_INFO.FSOCCP='No' 

                  GROUP BY EMPID,EMP_NAME,COMPANYNAME,DEPARTNAME,CARDEVENTDATE, 

                  REPORTING_MNGR,MNGR_EMAIL,EMP_EMAIL,ADMIN_MNGR_NAME,ADMIN_MNGR_MAIL);