This discussion is archived
1 Reply Latest reply: Feb 10, 2013 12:19 PM by Stew Ashton RSS

Column to row problem to order by elements

Yoav Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points