OraQA

Oracle Question and Answer

  • Do you have a solution to a problem? Do you have an unanswered question? Login and share it with the Oracle community. More...

Oracle News


Entries RSS feed

Comments RSS feed

How to do table column based data manipulation in SQL and XQuery

April 3rd, 2012 By Frank Zhou

The following SQL pattern can be used to stragg all the table column data together
The STRAGG(column data) is grouped together based on the table column names.
This SQL also manipulates comma-delimited strings using XQuery functionalities.


CREATE TABLE PERSON
(ID            NUMBER,
 FIRSTNAME     VARCHAR2(20 BYTE),
 MIDDLENAME    VARCHAR2(20 BYTE),
 LASTNAME      VARCHAR2(20 BYTE),
 LOCATION_ID   NUMBER);

INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME, LOCATION_ID)
VALUES (1, 'Frank', null, 'Zhou', 1);
INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME, LOCATION_ID)
VALUES (2, 'Peter', 'B',  'Lee', 1);
INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME, LOCATION_ID)
VALUES (3, 'John' , 'C',   'Adam', 2);
INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME, LOCATION_ID)
VALUES (4, 'Dave' , null, 'Washington', 2);

commit;

set long 10000;
variable tableName varchar2(30)
exec :tableName:='PERSON'

COLUMN col_name FORMAT  A12
COLUMN sort_no_dup_str FORMAT A25
COLUMN distinct_cnt FORMAT  A5
COLUMN reverse_order FORMAT A25


---------------------------SQL QUERY-------------------------



WITH DATA AS 
(SELECT DBMS_XMLGEN.getxmltype(
        (SELECT 'SELECT COLUMN_NAME, COLUMN_VALUES FROM (SELECT '||LISTAGG(
                'XMLAGG(XMLFOREST('||COLUMN_NAME||' AS '||COLUMN_NAME
                ||') ORDER BY ROWNUM) AS '||COLUMN_NAME,' , ') WITHIN GROUP(ORDER BY NULL)
                ||' FROM '||MAX(TABLE_NAME)
                ||') UNPIVOT INCLUDE NULLS(COLUMN_VALUES for COLUMN_NAME in ('
                ||LISTAGG (COLUMN_NAME, ',') WITHIN GROUP (ORDER BY COLUMN_ID)
                ||'))' AS SQL_STR
          FROM USER_TAB_COLUMNS
          WHERE TABLE_NAME = UPPER(:TableName))) AS XML
FROM DUAL),
ROW_DATA AS 
(SELECT COLUMN_VALUE AS ROW_XML
 FROM DATA D, XMLTABLE('for $i in $str/ROWSET/ROW return $i'
                        PASSING D.XML AS "str")
),
SORT_REMOVE_DUPLICATE AS
(SELECT XMLQUERY('$str/ROW/COLUMN_NAME/text()'
                  PASSING RD.ROW_XML AS "str" 
                  RETURNING CONTENT) AS col_name,
        XMLQUERY('string-join(for $i in  fn:distinct-values($str/ROW/COLUMN_VALUES/*[text()]) 
                  order by $i descending empty least return $i, ",")'
                  PASSING RD.ROW_XML AS "str" 
                  RETURNING CONTENT) AS sort_no_dup_str
FROM ROW_DATA RD),
COLUM_DATA_INFO AS 
(SELECT col_name, sort_no_dup_str,
  XMLQUERY('fn:count(fn:distinct-values(ora:tokenize($str, ",")))-1'
            PASSING CONCAT(s.sort_no_dup_str, ',') AS "str"
            RETURNING CONTENT) AS distinct_cnt,
 XMLQUERY ('string-join(remove(fn:reverse(ora:tokenize($str, ",") ), 1), ",")'
            PASSING CONCAT(s.sort_no_dup_str, ',') AS "str"
            RETURNING CONTENT) AS reverse_order
FROM SORT_REMOVE_DUPLICATE S
)
SELECT col_name, sort_no_dup_str, distinct_cnt, reverse_order
FROM COLUM_DATA_INFO;





COL_NAME     SORT_NO_DUP_STR           DISTI REVERSE_ORDER
------------ ------------------------- ----- -------------------------
ID           4,3,2,1                   4     1,2,3,4
FIRSTNAME    Peter,John,Frank,Dave     4     Dave,Frank,John,Peter
MIDDLENAME   C,B                       2     B,C
LASTNAME     Zhou,Washington,Lee,Adam  4     Adam,Lee,Washington,Zhou
LOCATION_ID  2,1                       2     1,2

SQL>

Leave a Reply

You must be logged in to post a comment.

RSS feed for comments on this question