1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Getting all the data in a one row.

Discussion in 'SQL PL/SQL' started by atirtil, Jun 16, 2013.

  1. atirtil

    atirtil Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    ..................
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You will need to use PL/SQL to do that, it appears, as the concatenation ends when the data for the current row ends; PL/SQL can allow you to populate a variable with multiple rows of data all concatenated together. As an example:

    Code (SQL):
    SQL> SELECT empno||' '||ename||' '||job||' '||mgr||' '||sal||' '||comm||' '||deptno
      2  FROM emp;
     
    EMPNO||''||ENAME||''||JOB||''||MGR||''||SAL||''||COMM||''||DEPTNO
    --------------------------------------------------------------------------------------------------------------------------------------------
    -------------------------------------------------------------------------------------
    7369 SMITH CLERK 7902 800  20
    7499 ALLEN SALESMAN 7698 1600 300 30
    7521 WARD SALESMAN 7698 1250 500 30
    7566 JONES MANAGER 7839 2975  20
    7654 MARTIN SALESMAN 7698 1250 1400 30
    7698 BLAKE MANAGER 7839 2850  30
    7782 CLARK MANAGER 7839 2450  10
    7788 SCOTT ANALYST 7566 3000  20
    7839 KING PRESIDENT  5000  10
    7844 TURNER SALESMAN 7698 1500 0 30
    7876 ADAMS CLERK 7788 1100  20
     
    EMPNO||''||ENAME||''||JOB||''||MGR||''||SAL||''||COMM||''||DEPTNO
    --------------------------------------------------------------------------------------------------------------------------------------------
    -------------------------------------------------------------------------------------
    7900 JAMES CLERK 7698 950  30
    7902 FORD ANALYST 7566 3000  20
    7934 MILLER CLERK 7782 1300  10
     
    14 ROWS selected.
     
    SQL>
    SQL> DECLARE
      2          emprec varchar2(32767);
      3
      4          cursor get_empdat IS
      5          SELECT empno||' '||ename||' '||job||' '||mgr||' '||sal||' '||comm||' '||deptno empdat
      6          FROM emp;
      7
      8  BEGIN
      9          FOR empr IN get_empdat loop
     10                  IF emprec IS NULL THEN
     11                          emprec:=empr.empdat;
     12                  ELSE
     13                          emprec:=emprec||' '||empr.empdat;
     14                  END IF;
     15          END loop;
     16
     17          dbms_output.put_line('=========================================================================================================
    ============================================================================================================================================
    =========================================='
    );
     18          dbms_output.put_line('EMPNO     ||ENAME     ||JOB      ||MGR       ||SAL       ||COMM      ||DEPTNO    ||EMPNO     ||ENAME
    ||JOB   ||MGR       ||SAL       ||COMM      ||DEPTNO     ||EMPNO     ||ENAME     ||JOB       ||MGR   ||SAL           ||COMM      ||DEPTNO'
    );
     19          dbms_output.put_line('=========================================================================================================
    ============================================================================================================================================
    =========================================='
    );
     20          dbms_output.put_line(emprec);
     21  END;
     22  /
    ============================================================================================================================================
    ============================================================================================================================================
    =======
    EMPNO     ||ENAME     ||JOB      ||MGR       ||SAL       ||COMM      ||DEPTNO    ||EMPNO     ||ENAME  ||JOB          ||MGR       ||SAL
     ||COMM  ||DEPTNO     ||EMPNO     ||ENAME     ||JOB       ||MGR       ||SAL       ||COMM      ||DEPTNO
    ============================================================================================================================================
    ============================================================================================================================================
    =======
    7369 SMITH CLERK 7902 800  20 7499 ALLEN SALESMAN 7698 1600 300 30 7521 WARD SALESMAN 7698 1250 500 30 7566 JONES MANAGER 7839 2975  20 7654
     MARTIN SALESMAN 7698 1250 1400 30 7698 BLAKE MANAGER 7839 2850  30 7782 CLARK MANAGER 7839 2450  10 7788 SCOTT ANALYST 7566 3000  20 7839 K
    ING PRESIDENT  5000  10 7844 TURNER SALESMAN 7698 1500 0 30 7876 ADAMS CLERK 7788 1100  20 7900 JAMES CLERK 7698 950  30 7902 FORD ANALYST 7
    566 3000  20 7934 MILLER CLERK 7782 1300  10
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
     
     
    atirtil likes this.
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It doesn't appear that you are using Oracle to generate this list (the column headers are not Oracle headers). Which database are you using?
     
  4. Jyoti

    Jyoti Active Member

    Messages:
    2
    Likes Received:
    1
    Trophy Points:
    65
    Hi,

    Please try below one ..... :)

    SELECT LISTAGG("BRE_INV1", chr(10)) WITHIN GROUP (ORDER BY "BRE_INV1") AS BRE_INV1,
    LISTAGG("BRE_NUMINV1", chr(10)) WITHIN GROUP (ORDER BY "BRE_NUMINV1") AS BRE_NUMINV1,
    LISTAGG("BRE_NOMINV1", chr(10)) WITHIN GROUP (ORDER BY "BRE_NOMINV1") AS BRE_NOMINV1
    LISTAGG("BRE_NOMPREINV1", chr(10)) WITHIN GROUP (ORDER BY "BRE_NOMPREINV1") AS BRE_NOMPREINV1 FROM
    (SELECT
    DECODE(BREINV1.NAMEKEY, NULL,'0','1') "BRE_INV1",
    DECODE(BREINV1.NAMEKEY, NULL,' ',BREINV1.SEQUENCE) "BRE_NUMINV1",
    DECODE(BREINV1.NAMEKEY, NULL, ' ', DECODE(BREINV1.SEQUENCE,NULL,NULL,RTRIM(ADDINV1.FORMATTEDADDRESS,CHR(0))||CHR(13) || CHR(10) ||'Citizen of ' ||SUBSTR(PAY1.COUNTRYDESCRIPTION,1,30))) "BRE_NOMINV1",
    INDIVIDU1.FIRSTNAME || ' ' || INV1.NAME "BRE_NOMPREINV1"
    FROM NAME INV1
    LEFT JOIN CASENAME BREINV1 ON BREINV1.NAMEKEY = INV1.NAMEKEY
    LEFT JOIN NAMEADDRESS ADDINV1 ON ADDINV1.NAMEKEY = INV1.NAMEKEY
    LEFT JOIN NAMEINDIVIDUAL INDIVIDU1 ON INV1.NAMEKEY = INDIVIDU1.NAMEKEY
    LEFT JOIN TABLECOUNTRY PAY1 ON INDIVIDU1.NATIONALITYCOUNTRYKEY = PAY1.COUNTRYKEY
    WHERE
    ADDINV1.addresstypekey = 1000 AND
    BREINV1.nametypekey = 209 AND
    BREINV1.CASEKEY = 1397691)
     
    atirtil likes this.
  5. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi,

    Try this: (but there's still a difference from what u want) ;)
    Code (SQL):

    SELECT  *
    FROM    (SELECT DECODE(BREINV1.NAMEKEY, NULL,'0','1') BRE_INV,
                    DECODE(BREINV1.NAMEKEY, NULL,' ',BREINV1.SEQUENCE) BRE_NUMINV,
                    DECODE(BREINV1.NAMEKEY, NULL, ' ', DECODE(BREINV1.SEQUENCE,NULL,NULL,RTRIM(ADDINV1.FO RMATTEDADDRESS,CHR(0))||CHR(13) || CHR(10) ||'Citizen of ' ||SUBSTR(PAY1.COUNTRYDESCRIPTION,1,30))) BRE_NOMINV,
                    INDIVIDU1.FIRSTNAME || ' ' || INV1.NAME BRE_NOMPREINV,
                    ROWNUM  SRL
             FROM   NAME INV1
                        LEFT JOIN CASENAME BREINV1 ON BREINV1.NAMEKEY = INV1.NAMEKEY
                        LEFT JOIN NAMEADDRESS ADDINV1 ON ADDINV1.NAMEKEY = INV1.NAMEKEY
                        LEFT JOIN NAMEINDIVIDUAL INDIVIDU1 ON INV1.NAMEKEY = INDIVIDU1.NAMEKEY
                        LEFT JOIN TABLECOUNTRY PAY1 ON INDIVIDU1.NATIONALITYCOUNTRYKEY = PAY1.COUNTRYKEY
             WHERE  ADDINV1.addresstypekey = 1000
             AND    BREINV1.nametypekey = 209
             AND    BREINV1.CASEKEY = 1397691
            )
            PIVOT   (MIN(BRE_INV)       AS  "BRE_INV",
                     MIN(BRE_NUMINV)    AS  "BRE_NUMINV",
                     MIN(BRE_NOMINV)    AS  "BRE_NOMINV",
                     MIN(BRE_NOMPREINV) AS  "BRE_NOMPREINV"
                     FOR (SRL) IN ('1','2')    
                    );
     
     
    atirtil likes this.
  6. atirtil

    atirtil Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    just need to define maybe the loop until the end of the rows but not 1,2 and it should be great :)
     
  7. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi,

    If u don't wanna hard-code it to "1,2" then u'll have to use Pivot XML with parameter as ANY or SUB-QUERY and later convert those XML values into char. Link

    Another option is, u form the query dynamically through PL/SQL and then use execute immediate bulk collect to get the values.
     
  8. atirtil

    atirtil Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    I will use it like 1,2,3,4,5,6,7 I wont loose time with that however I am creating bookmark in a microsoft word document with the name of these columns aliases and word doesnt allow me that a bookmark starts with a number
    I know that is stupid but I need these numbers in the end of column aliases :)
    will try to handle that way if it is not possible what I say :9

    thanks thousand times :)
     
  9. atirtil

    atirtil Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    well that would work maybe if I add a new select to the head of the query and rename the columns
    Code (Text):

    SELECT 1_BRE_INV
    FROM
    (
        SELECT *

             FROM   (
                        SELECT DECODE(BREINV1.NAMEKEY, NULL,'0','1') BRE_INV,
                        DECODE(BREINV1.NAMEKEY, NULL,' ',BREINV1.SEQUENCE) BRE_NUMINV,
                        DECODE(BREINV1.NAMEKEY, NULL, ' ', DECODE(BREINV1.SEQUENCE,NULL,NULL,RTRIM(ADDINV1.FORMATTEDADDRESS,CHR(0))||CHR(13) || CHR(10) ||'Citizen of ' ||SUBSTR(PAY1.COUNTRYDESCRIPTION,1,30))) BRE_NOMINV,
                        INDIVIDU1.FIRSTNAME || ' ' || INV1.NAME BRE_NOMPREINV,
                        ROWNUM  SRL
                     FROM NAME INV1
                        LEFT JOIN CASENAME BREINV1 ON BREINV1.NAMEKEY = INV1.NAMEKEY
                        LEFT JOIN NAMEADDRESS ADDINV1 ON ADDINV1.NAMEKEY = INV1.NAMEKEY
                        AND ADDINV1.addresstypekey = 1000
                        LEFT JOIN NAMEINDIVIDUAL INDIVIDU1 ON INV1.NAMEKEY = INDIVIDU1.NAMEKEY
                        LEFT JOIN TABLECOUNTRY PAY1 ON INDIVIDU1.NATIONALITYCOUNTRYKEY = PAY1.COUNTRYKEY
                     WHERE  
                        BREINV1.nametypekey = 209 AND
                        BREINV1.CASEKEY = 14410390
                    )  
                               
             PIVOT  (
                     MIN(BRE_INV) AS "BRE_INV",
                     MIN(BRE_NUMINV) AS "BRE_NUMINV",
                     MIN(BRE_NOMINV) AS "BRE_NOMINV",
                     MIN(BRE_NOMPREINV) AS "BRE_NOMPREINV"
                     FOR (SRL) IN (1,2,3,4,5,6,7,8,9,10,11,12)
                    )
                                 
                    )
     
    However, it does not work, can you see where I am making a fault?
    1_BRE_INV is the first column alias after executing the second SELECT statement
     
  10. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Just a note....the Jul/Aug 2013 issue of Oracle Magazine has an article ; "Pivotal Access to Your Data: Analytic Functions, concluded". Part 12 of a series on SQL.

    Page 45.


    CJ