1 Reply Latest reply: Feb 10, 2013 2:19 PM by Stew Ashton RSS

    Column to row problem to order by elements

    Yoav
      Hi,
      Version 10204.

      I am using xmlagg function in order convert cols to rows.
      SELECT *
      from DBA_LOG_GROUP_COLUMNS
       WHERE table_name IN  ('NAP_CUST_USERS')
      order by table_name   ,position ;
      
      OWNER                          LOG_GROUP_NAME                 TABLE_NAME                     COLUMN_NAME                      POSITION LOGGING_PROPERTY
      ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ------------------
      PSSYS                          GGS_14831759                   NAP_CUST_USERS                 SETID                                   1 LOG
      PSSYS                          GGS_14831759                   NAP_CUST_USERS                 COMPANYID                               2 LOG
      PSSYS                          GGS_14831759                   NAP_CUST_USERS                 USERNAME                                3 LOG
      PSSYS                          GGS_14831759                   NAP_CUST_USERS                 NAP_CLOSE_DATE                          4 LOG
      PSSYS                          GGS_14831759                   NAP_CUST_USERS                 PERSON_ID                               5 LOG
      Here is the way i am using this :
      SELECT table_name ,
      rtrim (xmlagg (xmlelement (e, column_name || ',')).extract ('//text()'), ',') enames
      from DBA_LOG_GROUP_COLUMNS e
       WHERE table_name IN  ('NAP_CUST_USERS')
      group by table_name
      order by table_name;
      
      NAP_CUST_USERS     SETID,COMPANYID,NAP_CLOSE_DATE,USERNAME,PERSON_ID
      As you can see the the order of the column in the second statment is different than in the first one.
      I would like to keep the column position, as they are in the first statment.
      Please note that there are several tables in the where clause.
      Just to keep the example simple as possible i mentioned only one table

      Thanks
        • 1. Re: Column to row problem to order by elements
          Stew Ashton
          How about using the ORDER BY clause of XMLAGG?
          http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb13gen.htm#ADXDB5084
          SELECT table_name ,
          RTRIM (XMLAGG (
            XMLELEMENT (E, COLUMN_NAME || ',')
            order by column_id
          ).EXTRACT ('//text()'), ',') ENAMES
          FROM USER_TAB_COLS E
          where table_name in ('EMP')
          group by table_name
          
          TABLE_NAME  ENAMES
          ----------- --------------------------------------------
          EMP         EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
          Edited by: Stew Ashton on Feb 10, 2013 9:18 PM