locked
oracle transform data problem RRS feed

  • Question

  • User-583959464 posted

    I have a view with 2 columns, 1st is the name, 2nd is the report as belows

    name   report_name
    col1   rpt1
    col3   rpt2
    col4   rpt1
    col5   rpt1




    how can I group the name by report_name as belows:

    report_name name 
    rpt1        col1,col4,col5
    rpt2        col3

     

    any sql script or PL/SQL can do that ?

    Sunday, November 5, 2017 10:25 AM

All replies

  • User269602965 posted

    Using Oracle 11gR2 or later

     SELECT REPORT_NAME, LISTAGG(NAME,', ') WITHIN GROUP (ORDER BY NAME) AS NAME_CSV FROM {yourtablename} GROUP BY REPORT_NAME;

    The problem is limitation of the list size.  if the CSV list gets to be more than 4000 characters, then you run into problems of Oracle limitations.

    If you anticipate that, you may have to CAST the listagg as CLOB datatype.

    Monday, November 6, 2017 1:22 AM
  • User-583959464 posted
    I am not asking about the size problem, I am asking how to transform data from the data file to my expected result , each row has only 1 file name, and concat all column belong to same file as below :

    report_name name
    file1 col1,col2
    file2 col3,col4
    Monday, November 6, 2017 3:01 AM
  • User269602965 posted

    And you were given the code to transform for Oracle 11g R2 or later (12cR1, 12cR2).

    But yes, if the size of your CSV list grows beyond the column size limits, the query will fail.

    Monday, November 6, 2017 6:05 PM
  • User-583959464 posted
    anyone know how can I output belows result.

    report_name name
    rpt1 col1,col4,col5
    rpt2 col3
    Tuesday, November 7, 2017 12:53 AM
  • User269602965 posted

    Code below

    Tuesday, November 7, 2017 7:04 PM
  • User269602965 posted
    Create table LISTCSV
    (
    NAME        VARCHAR2(4),
    REPORT_NAME VARCHAR2(4)
    );
    
    INSERT INTO LISTCSV VALUES ('col1','rpt1');
    INSERT INTO LISTCSV VALUES ('col2','rpt2');
    INSERT INTO LISTCSV VALUES ('col3','rpt1');
    INSERT INTO LISTCSV VALUES ('col4','rpt1');
    
    COMMIT;
    
    SET LINESIZE 120;
    SET PAGESIZE 50000;
    COLUMN report_name FORMAT A12;
    COLUMN name        FORMAT A80;
    
    
    SELECT DISTINCT
      REPORT_NAME AS REPORT_NAME, 
      LISTAGG(NAME,', ') WITHIN GROUP (ORDER BY REPORT_NAME, NAME) OVER (PARTITION BY REPORT_NAME) AS NAME  
    FROM 
      LISTCSV 
    GROUP BY 
      REPORT_NAME, NAME
    ORDER BY
      REPORT_NAME, NAME;
    
    REPORT_NAME  NAME
    ------------ --------------------------------------------------------------------------------
    rpt1         col1, col3, col4
    rpt2         col2



    Wednesday, November 8, 2017 1:32 AM