This query will do the following data manipulation and transformation:

1) Rename the xml tag <FirstName> to <FN> when the position of the element node is
either the last one or the id attribute is 3.

2) Remove the <MiddleName> tag if this is empty.

3) Transform all the xml attributes to elements.

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);

CREATE TABLE LOCATION
(LOCATION_ID       NUMBER,
 LOCATION_NAME     VARCHAR2(20 BYTE)
 );

INSERT INTO LOCATION (LOCATION_ID, LOCATION_NAME)
VALUES (1, 'BOSTON');
INSERT INTO LOCATION (LOCATION_ID, LOCATION_NAME)
VALUES (2, 'NEW YORK');
COMMIT;

---------------Input XML Data is generated by the top part of the query (WITH DATA AS)-----------------

<ROWSET>
  <ROW ID="1" Location_name="BOSTON">
    <FirstName>Frank</FirstName>
    <MiddleName/>
    <LastName>Zhou</LastName>
  </ROW>
  <ROW ID="2" Location_name="BOSTON">
    <FirstName>Peter</FirstName>
    <MiddleName>B</MiddleName>
    <LastName>Lee</LastName>
  </ROW>
  <ROW ID="3" Location_name="NEW YORK">
    <FirstName>John</FirstName>
    <MiddleName>C</MiddleName>
    <LastName>Adam</LastName>
  </ROW>
  <ROW ID="4" Location_name="NEW YORK">
    <FirstName>Dave</FirstName>
    <MiddleName/>
    <LastName>Washington</LastName>
  </ROW>
</ROWSET>

---------------------------------------------------SQL Query------------------------------------

WITH DATA AS
(SELECT XMLPARSE(DOCUMENT XMLSERIALIZE(CONTENT
           XMLELEMENT(ROWSET,
               XMLAGG(XMLELEMENT("ROW",
                      XMLATTRIBUTES(ID AS "ID",  LOCATION_NAME AS "Location_name"),
                      XMLELEMENT("FirstName",    FIRSTNAME),
                      XMLELEMENT("MiddleName",   MIDDLENAME),
                      XMLELEMENT( "LastName",    LASTNAME  )
                      )  ORDER BY ID)
                 )  )  ) AS XML
 FROM  person p, location l
 WHERE p.location_id = L.LOCATION_ID
)
SELECT  XMLSERIALIZE(DOCUMENT XMLTRANSFORM(
        (SELECT XML FROM DATA) ,
 XMLTYPE('<?xml version="1.0" encoding="ISO-8859-1"?>
 <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:strip-space elements="*"/>
 <xsl:output indent="yes"/>
 <xsl:template match="node()|@*">
        <xsl:copy><xsl:apply-templates select="node()|@*"/></xsl:copy>
 </xsl:template>
 <xsl:template match="ROW[position() = last() or @ID=3 ]/FirstName ">
        <FN><xsl:apply-templates select="node()|@*"/></FN>
 </xsl:template>
 <xsl:template match="ROW[not(MiddleName/text())]/MiddleName"/>
  <xsl:template match="@*">
        <xsl:element name="{name(.)}"><xsl:value-of select="."/></xsl:element>
  </xsl:template>
  </xsl:stylesheet>')))  AS XML_DATA
 FROM DUAL;

---------------------------------Output XML Data ---------------------------------------

<?xml version="1.0" encoding="utf-8"?>

<ROWSET>
 <ROW>
  <ID>1</ID>
  <Location_name>BOSTON</Location_name>
  <FirstName>Frank</FirstName>
  <LastName>Zhou</LastName>
 </ROW>
 <ROW>
  <ID>2</ID>
  <Location_name>BOSTON</Location_name>
  <FirstName>Peter</FirstName>
  <MiddleName>B</MiddleName>
  <LastName>Lee</LastName>
 </ROW>
 <ROW>
  <ID>3</ID>
  <Location_name>NEW YORK</Location_name>
  <FN>John</FN>
  <MiddleName>C</MiddleName>
  <LastName>Adam</LastName>
 </ROW>
 <ROW>
  <ID>4</ID>
  <Location_name>NEW YORK</Location_name>
  <FN>Dave</FN>
  <LastName>Washington</LastName>
 </ROW>
</ROWSET>

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>

COLUMN old_str  FORMAT  A28
COLUMN ordered_no_dup_chars FORMAT A23
COLUMN Reversed_chars FORMAT  A23

--------------------SQL Query-------------------

WITH data AS
(SELECT '3332221118888'   AS  old_str  FROM dual
  UNION ALL
  SELECT 'CCCBBBAAA'          FROM dual
   UNION ALL
  SELECT 'XXXYYY33332222'     FROM dual
)
SELECT old_str, ordered_no_dup_chars,
       XMLQUERY('fn:codepoints-to-string(fn:reverse(fn:string-to-codepoints($str)))'
                 PASSING XML.ordered_no_dup_chars AS "str"
                 RETURNING CONTENT) AS Reversed_chars
FROM data d,
XMLTable('fn:codepoints-to-string(for $i in fn:distinct-values(fn:string-to-codepoints($str))
           order by $i ascending empty least return $i)'
           PASSING d.old_str  AS "str"
           COLUMNS  ordered_no_dup_chars varchar2(4000) PATH '.'
) XML;

OLD_STR                      ORDERED_NO_DUP_CHARS    REVERSED_CHARS
---------------------------- ----------------------- -----------------------
3332221118888                1238                    8321
CCCBBBAAA                    ABC                     CBA
XXXYYY33332222               23XY                    YX32

SQL>

set long 10000;
variable url_input varchar2(500)
exec :url_input:='http://oraqa.com/2012/02/27/how-to-transform-a-csv-file-into-html-table-in-sql/'

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

WITH HTML_TAB AS
(SELECT CAST(REGEXP_REPLACE(SUBSTR(STR, 0, REGEXP_INSTR(str,'</table>', 1,1,1, 'i') ), '( ){2,}', ' ')
         AS VARCHAR2(4000)) AS html_str
 FROM
 (SELECT SUBSTR(str, REGEXP_INSTR(str,'<table', 1,1,0,'i')) as str
  FROM
 (SELECT HTTPURITYPE(:url_input).GETCLOB( ) AS str
  FROM DUAL )
 )
),
HEADER AS
(SELECT col_name, num,
        XMLCAST(XMLQUERY('fn:count(/table/tr/th)'
                        PASSING XMLPARSE(DOCUMENT html_str)
                        RETURNING CONTENT) AS NUMBER) AS Col_NUM
 FROM HTML_TAB, XMLTABLE('/table/tr/th'
                         PASSING XMLPARSE(DOCUMENT html_str)
                         COLUMNS col_name VARCHAR2 (30) PATH '.' ,
                         num FOR ORDINALITY)
),
H_DATA AS
(SELECT LISTAGG(col_name, ',') WITHIN GROUP(ORDER BY num) AS COL_NAMES,
        MAX(Col_NUM) AS Col_NUM
  FROM HEADER
),
COLUM_DATA AS
(SELECT rn, col_data||',' AS col_data
 FROM HTML_TAB, XMLTABLE('/table/tr/td'
                         PASSING XMLPARSE(DOCUMENT html_str)
                         COLUMNS  col_data VARCHAR2 (30) PATH '.' ,
                         rn FOR ORDINALITY )
 ),
CSV AS
(SELECT LISTAGG(col_data, '') WITHIN GROUP (ORDER BY rn) AS str, MAX(rn) AS RANK
 FROM COLUM_DATA D, H_DATA H
 GROUP BY CEIL(D.rn/H.Col_NUM)
)
SELECT CSV_STR
FROM (SELECT COL_NAMES AS CSV_STR, 0 AS pos, TO_NUMBER(NULL) AS RANK
      FROM H_DATA
      UNION ALL
      SELECT SUBSTR(str, 0, LENGTH (str) - 1), 1, RANK FROM CSV)
ORDER BY pos, RANK;

CSV_STR
——————————————————————————–

ID,FIRSTNAME,MIDDLENAME,LASTNAME
1,Frank,,Zhou
2,Peter,B,Lee
3,John,C,Adam
4,Dave,,Washington

SELECT Col AS name_x, Function() AS name_y, Analytical_function( ) AS name_z FROM ( ***ANYTHING*** )

You can change your query into this required format by wrapping your sql inside an inline view if needed. The “AS” keyword and column_name “alias” are required for building the XML tags.

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);

CREATE TABLE LOCATION
(LOCATION_ID       NUMBER,
 LOCATION_NAME     VARCHAR2(20 BYTE)
 );

INSERT INTO LOCATION (LOCATION_ID, LOCATION_NAME)
VALUES (1, 'BOSTON');
INSERT INTO LOCATION (LOCATION_ID, LOCATION_NAME)
VALUES (2, 'NEW YORK');
COMMIT;

set long 10000;
variable sql_input varchar2(500)
BEGIN
   :sql_input:= q'[
     SELECT b.location_name AS location,
            COUNT (*) AS COUNT,
            LISTAGG (a.lastname, ' , ') WITHIN GROUP (ORDER BY a.id)
               AS lastname_stragg
       FROM person a, location b
      WHERE a.location_id = b.location_id
   GROUP BY ROLLUP (b.location_name)
     HAVING MAX (b.location_id) > 0
   ]';
END;
/
------------------------------------------SQL QUERY------------------------------------------------------------
WITH SQL_QUERY AS
(SELECT UPPER(SUBSTR(sql_str, 0, REGEXP_INSTR(sql_str, ' FROM', 1,1,0, 'i'))) AS SELECT_STR,
              SUBSTR(sql_str, REGEXP_INSTR(sql_str, ' FROM', 1,1,0, 'i')) AS FROM_STR
 FROM
 (SELECT DBMS_XMLGEN.CONVERT( XMLQUERY('fn:substring(fn:normalize-space(str), 8) '
                              PASSING XMLELEMENT("str",  SQL_STR)
                              RETURNING CONTENT).GETSTRINGVAL(), 1) AS sql_str
  FROM (SELECT :sql_input as sql_str FROM dual )
 )
),
COL_NAMES AS
(SELECT SUBSTR(COL_NAME, 0, INSTR(COL_NAME, ' AS ')) AS VAL,
        SUBSTR(COL_NAME, INSTR(COL_NAME, ' AS ') + 4) AS ALIAS, ROWNUM rn
 FROM SQL_QUERY, XMLTABLE('ora:tokenize(.,"#")'
                 PASSING  REGEXP_REPLACE (SELECT_STR, '([[:space:]]AS[[:space:]][^,]+)[[:space:]]?[,]?' , '\1#')
                 COLUMNS COL_NAME VARCHAR2 (100) PATH '.')
),
XML_QUERY AS
(SELECT ' SELECT 1, XMLELEMENT(ROW_DATA, '||LISTAGG(REPLACE(VAL, VAL, 'XMLELEMENT('||ALIAS||' , '||VAL||')') , ' , ')
                                            WITHIN GROUP (ORDER BY rn)||')' AS SELECT_STR,
          MAX((SELECT FROM_STR FROM SQL_QUERY)) AS FROM_AND_CONDITIONS_STR
  FROM  COL_NAMES
),
XML_DATA AS
(SELECT DBMS_XMLGEN.GETXMLTYPE(DBMS_XMLGEN.NEWCONTEXTFROMHIERARCHY(
                               SELECT_STR||' '||FROM_AND_CONDITIONS_STR)) AS XML_DOC
 FROM XML_QUERY
)
SELECT XMLSERIALIZE(DOCUMENT XMLTRANSFORM(
(SELECT XMLELEMENT (ROWSET, XML_DOC) FROM XML_DATA),
 XMLTYPE (
'<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html"/>
<xsl:template match="/">
     <html><body><xsl:apply-templates/> </body></html>
</xsl:template>
<xsl:template match="ROWSET">
     <table border = "1" cellspacing = "0"> <tr> <xsl:apply-templates select ="ROW_DATA[1]/*" mode ="Col_Name"/> </tr>
     <xsl:apply-templates/> </table>
</xsl:template>
<xsl:template match="ROW_DATA">
     <tr><xsl:apply-templates/> </tr>
</xsl:template>
<xsl:template match="ROW_DATA/*">
     <td><xsl:apply-templates/> </td>
</xsl:template>
<xsl:template match="ROW_DATA/*"  mode = "Col_Name">
      <th><xsl:value-of select ="name(.)"/> </th>
</xsl:template>
      </xsl:stylesheet>'))) AS HTML_TABL_DATA
FROM DUAL;              

HTML_TABL_DATA
——————————————————————————–

SELECT Col AS name_x, Function() AS name_y, Analytical_function( ) AS name_z FROM ( ***ANYTHING*** )

You can change your own query into this required format by wrapping your SQL inside an inline view if needed. The “AS” keyword and column_name “alias” are required for building the XML tags.

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);

CREATE TABLE LOCATION
(LOCATION_ID       NUMBER,
 LOCATION_NAME     VARCHAR2(20 BYTE)
 );

INSERT INTO LOCATION (LOCATION_ID, LOCATION_NAME)
VALUES (1, 'BOSTON');
INSERT INTO LOCATION (LOCATION_ID, LOCATION_NAME)
VALUES (2, 'NEW YORK');
COMMIT;

set long 10000;
variable sql_input varchar2(3000)
BEGIN
 :sql_input := q'[
      SELECT b.location_name AS location,
             COUNT (*) AS COUNT,
             LISTAGG (a.lastname, '__') WITHIN GROUP (ORDER BY a.id)
                AS lastname_stragg
        FROM person a, location b
       WHERE a.location_id = b.location_id
    GROUP BY ROLLUP (b.location_name)
      HAVING MAX (b.location_id) > 0
         ]';
END;
/

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

WITH SQL_QUERY AS
(SELECT UPPER(SUBSTR(sql_str, 0, REGEXP_INSTR(sql_str, ' FROM', 1,1,0, 'i'))) AS SELECT_STR,
              SUBSTR(sql_str, REGEXP_INSTR(sql_str, ' FROM', 1,1,0, 'i')) AS FROM_STR
 FROM
 (SELECT DBMS_XMLGEN.CONVERT( XMLQUERY('fn:substring(fn:normalize-space(str), 8) '
                              PASSING XMLELEMENT("str",  SQL_STR)
                              RETURNING CONTENT).GETSTRINGVAL(), 1) AS sql_str
  FROM (SELECT :sql_input as sql_str FROM dual )
 )
),
COL_NAMES AS
(SELECT SUBSTR(COL_NAME, 0, INSTR(COL_NAME, ' AS ')) AS VAL,  ROWNUM rn,
        SUBSTR(COL_NAME, INSTR(COL_NAME, ' AS ') + 4) AS ALIAS,  COUNT (*) OVER ()-1 AS CNT
 FROM SQL_QUERY, XMLTABLE('ora:tokenize(.,"#")'
                 PASSING  REGEXP_REPLACE (SELECT_STR, '([[:space:]]AS[[:space:]][^,]+)[[:space:]]?[,]?' , '\1#')
                 COLUMNS COL_NAME VARCHAR2 (100) PATH '.')
),
XML_QUERY AS
(SELECT 'SELECT 1, XMLELEMENT(ROW_DATA, '||LISTAGG(REPLACE(VAL, VAL, 'XMLELEMENT('||ALIAS||' , '||VAL||')'), ' , ')
                                            WITHIN GROUP (ORDER BY rn)||')' AS SELECT_STR, MAX(CNT) AS CNT,
          MAX((SELECT FROM_STR FROM SQL_QUERY)) AS FROM_AND_CONDITIONS_STR
  FROM  COL_NAMES
),
XML_DATA AS
(SELECT DBMS_XMLGEN.GETXMLTYPE(DBMS_XMLGEN.NEWCONTEXTFROMHIERARCHY(
                               SELECT_STR||' '||FROM_AND_CONDITIONS_STR)) AS XML_DOC, CNT
 FROM XML_QUERY
),
TAB_DATA AS
(SELECT str ||',' AS str, CEIL(ROWNUM/A.CNT) AS grp, ROWNUM AS RANK
 FROM XML_DATA A, XMLTABLE ('ROW_DATA/*'
                             PASSING A.XML_DOC
                             COLUMNS str VARCHAR2 (100) PATH '.')
),
CSV AS
(SELECT LISTAGG(str, '') WITHIN GROUP (ORDER BY RANK) AS str, MAX(RANK) AS RANK
 FROM TAB_DATA
 GROUP BY GRP
)
SELECT CSV_STR
FROM (SELECT LISTAGG(ALIAS, ',') WITHIN GROUP (ORDER BY rn) AS CSV_STR, 0 AS pos, TO_NUMBER (NULL) AS RANK
      FROM COL_NAMES
      UNION ALL
     SELECT SUBSTR (str, 0, LENGTH (str) - 1), 1, RANK FROM CSV)
ORDER BY pos, RANK;

CSV_STR
--------------------------------------------------------------------------------

LOCATION,COUNT,LASTNAME_STRAGG
BOSTON,2,Zhou__Lee
NEW YORK,2,Adam__Washington
,4,Zhou__Lee__Adam__Washington

create or replace directory tmp as '/tmp';

create a word.csv file.

C:\tmp>more word.csv

ID,FIRSTNAME,MIDDLENAME,LASTNAME
1,Frank,,Zhou
2,Peter,B,Lee
3,John,C,Adam
4,Dave,,Washington

CREATE TABLE CSV_FILE (csv_str VARCHAR2(3000))
     ORGANIZATION EXTERNAL
     (
       TYPE ORACLE_LOADER
       DEFAULT DIRECTORY tmp
       ACCESS PARAMETERS
       (
         records delimited by newline
         fields terminated by whitespace
         missing field values are null
         ( csv_str )
       )
       LOCATION ('word.csv')
     )
     REJECT LIMIT UNLIMITED;

set long 10000;
variable col_name_input varchar2(300)
exec :col_name_input:='ID,FIRSTNAME,MIDDLENAME,LASTNAME'

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

WITH TAB AS
(SELECT CSV_STR, ROWNUM AS ROW_RN, REGEXP_COUNT(:col_name_input, ',')+1 AS COL_NUM,
        UPPER(:col_name_input) AS col_name_input
 FROM CSV_FILE
),
COL_NAME AS
(SELECT REPLACE(COL_NAME, COL_NAME, '<'||COL_NAME||'>')  AS OPEN_TAG,
        REPLACE(COL_NAME, COL_NAME, '</'||COL_NAME||'>') AS CLOSE_TAG,
        MOD(ROWNUM, COL_NUM) col_rn,  ROWNUM rn
 FROM TAB,  XMLTABLE ('ora:tokenize(.,",")'
                       PASSING  CSV_STR
                       COLUMNS "COL_NAME" VARCHAR2 (100) PATH '.')
 WHERE UPPER(CSV_STR) = col_name_input
),
CSV_DATA AS
(SELECT COL_DAT, MOD(ROWNUM, COL_NUM) AS  dat_rn, ROW_RN
 FROM TAB,  XMLTABLE ('ora:tokenize(.,",")'
                       PASSING  CSV_STR
                       COLUMNS COL_DAT VARCHAR2 (100) PATH '.')
 WHERE UPPER(CSV_STR) != col_name_input
),
XML_ROW AS
(SELECT REPLACE(LISTAGG(C.OPEN_TAG||D.COL_DAT||C.CLOSE_TAG,' ')
                WITHIN GROUP(ORDER BY C.rn ),' ') AS XML_DAT
 FROM CSV_DATA D,  COL_NAME  C
 WHERE C.col_rn = D.dat_rn
 GROUP BY D.ROW_RN
),
XML_DOC AS
(SELECT XMLAGG(XMLPARSE(DOCUMENT  '<ROW_DATA>'||XML_DAT||'</ROW_DATA>') ) XML_STR
 FROM XML_ROW
)
SELECT XMLQuery(
  'for  $i  in   /ROWSET
   return  <html><body><table border = "1" cellspacing = "0"> <tr>
  {       for $n  in  $i/ROW_DATA[1]/*
          return <th> { $n/name( ) } </th>
  } </tr>
  {       for $j  in  $i/ROW_DATA
          return <tr>
          { for  $k  in  $j/*
              return <td> { data($k) } </td>
          }
          </tr>
  }
  </table></body></html>'
 PASSING  XMLELEMENT("ROWSET", XML_STR)
 RETURNING  CONTENT).getCLOBVal()   AS HTML_TABLE_OUTPUT
 FROM (SELECT XML_STR FROM XML_DOC);

HTML_TABLE_OUTPUT
——————————————————————————–

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

INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME)
VALUES (1, 'Frank', null, 'Zhou');

INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME)
VALUES (2, 'Peter', 'B',  'Lee');

INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME)
VALUES (3, 'John' , 'C',   'Adam');

INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME)
VALUES (4, 'Dave' , null, 'Washington');
COMMIT; 

set long 10000;
variable tableName varchar2(30)
variable tableColumnName varchar2(300)
exec :tableName:='PERSON'
exec :tableColumnName:='ID,FIRSTNAME,MIDDLENAME,LASTNAME'

 -------------------------------------------SQL Query--------------------------------------------

 WITH Colum AS (SELECT UPPER (:tableColumnName) AS colum_input FROM DUAL),
     ColumnTab
     AS (    SELECT "COL_NAME", ROWNUM rn
               FROM Colum,
                    XMLTABLE ('ora:tokenize(.,",")'
                              PASSING Colum.colum_input
                              COLUMNS "COL_NAME" VARCHAR2 (100) PATH '.')),
     DATA
     AS (SELECT REPLACE (
                   column_name,
                   column_name,
                      'XMLELEMENT('
                   || column_name
                   || ' , '
                   || column_name
                   || ')')
                   column_name,
                table_name,
                rn
           FROM ALL_TAB_COLUMNS, ColumnTab
          WHERE     owner = USER
                AND table_name = UPPER (:tableName)
                AND column_name = COL_NAME),
     XML_QUERY
     AS (SELECT MAX (table_name) table_name,
                   ' SELECT LEVEL, XMLELEMENT (ROW_DATA ,'
                || LISTAGG (column_name, ' , ') WITHIN GROUP (ORDER BY rn)
                || ') FROM '
                   AS SQL_STR
           FROM DATA),
     XML_DATA
     AS (SELECT XMLELEMENT (
                   ROWSET,
                   DBMS_XMLGEN.getxmltype (
                      DBMS_XMLGEN.newcontextfromhierarchy (
                            SQL_STR
                         || ' '
                         || table_name
                         || ' CONNECT BY LEVEL<2')))
                   AS XML_DOC
           FROM XML_QUERY)
SELECT XMLSERIALIZE (
          DOCUMENT XMLTRANSFORM (
                      (SELECT XML_DOC FROM XML_DATA),
                      XMLTYPE (
                         '<?xml version="1.0" encoding="ISO-8859-1"?>
 <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:output method="html"/>
 <xsl:template match="/">
        <html><body><xsl:apply-templates/> </body></html>
 </xsl:template>
 <xsl:template match="ROWSET">
 <table border = "1" cellspacing = "0"> <tr> <xsl:apply-templates select ="ROW_DATA[1]/*" mode ="Col_Name"/> </tr>
 <xsl:apply-templates/> </table>
 </xsl:template>
 <xsl:template match="ROW_DATA">
         <tr><xsl:apply-templates/> </tr>
 </xsl:template>
 <xsl:template match="ROW_DATA/*">
         <td><xsl:apply-templates/> </td>
 </xsl:template>
 <xsl:template match="ROW_DATA/*"  mode = "Col_Name">
         <th><xsl:value-of select ="name(.)"/> </th>
 </xsl:template>
 </xsl:stylesheet>')))
          AS HTML_TABL_DATA
  FROM DUAL;

HTML_TABL_DATA
——————————————————————————–

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

INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME)
VALUES (1, 'Frank', null, 'Zhou');

INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME)
VALUES (2, 'Peter', 'B',  'Lee');

INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME)
VALUES (3, 'John' , 'C',   'Adam');

INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME)
VALUES (4, 'Dave' , null, 'Washington');

COMMIT; 

set long 10000;
variable tableName varchar2(30)
variable tableColumnName varchar2(300)
exec :tableName:='PERSON'
exec :tableColumnName:='ID,FIRSTNAME,MIDDLENAME,LASTNAME'

------------------------------------------------SQL Query---------------------------------------------  

WITH Colum AS (SELECT UPPER (:tableColumnName) AS colum_input FROM DUAL),
     ColumnTab
     AS (    SELECT "COL_NAME", ROWNUM rn
               FROM Colum,
                    XMLTABLE ('ora:tokenize(.,",")'
                              PASSING Colum.colum_input
                              COLUMNS "COL_NAME" VARCHAR2 (100) PATH '.')),
     DATA
     AS (SELECT table_name,
                rn,
                COUNT (*) OVER () AS CNT,
                REPLACE (
                   column_name,
                   column_name,
                      'XMLELEMENT('
                   || column_name
                   || ' , '
                   || column_name
                   || ')')
                   column_name
           FROM ALL_TAB_COLUMNS, ColumnTab
          WHERE     owner = USER
                AND table_name = UPPER (:tableName)
                AND column_name = COL_NAME),
     XML_QUERY
     AS (SELECT MAX (table_name) AS table_name,
                MAX (CNT) AS CNT,
                   ' SELECT LEVEL, XMLELEMENT(ROW_DATA ,'
                || LISTAGG (column_name, ' , ') WITHIN GROUP (ORDER BY rn)
                || ') FROM '
                   AS SQL_STR
           FROM DATA),
     XML_DATA
     AS (SELECT CNT,
                DBMS_XMLGEN.getxmltype (
                   DBMS_XMLGEN.newcontextfromhierarchy (
                      SQL_STR || ' ' || table_name || '  CONNECT BY LEVEL <2'))
                   AS XML_DOC
           FROM XML_QUERY),
     TAB_DATA
     AS (SELECT str || ',' AS str, CEIL (ROWNUM / A.CNT) grp, ROWNUM AS RANK
           FROM XML_DATA A,
                XMLTABLE ('ROW_DATA/*'
                          PASSING A.XML_DOC
                          COLUMNS str VARCHAR2 (100) PATH '.')),
     CSV
     AS (  SELECT LISTAGG (str, '') WITHIN GROUP (ORDER BY RANK) str,
                  MAX (RANK) AS RANK
             FROM TAB_DATA
         GROUP BY GRP)
  SELECT CSV_STR
    FROM (SELECT colum_input AS CSV_STR, 0 AS pos, TO_NUMBER (NULL) AS RANK
            FROM Colum
          UNION ALL
          SELECT SUBSTR (str, 0, LENGTH (str) - 1), 1, RANK FROM CSV)
ORDER BY pos, RANK;

CSV_STR
--------------------------------------------------------------------------------
ID,FIRSTNAME,MIDDLENAME,LASTNAME
1,Frank,,Zhou
2,Peter,B,Lee
3,John,C,Adam
4,Dave,,Washington

select a.id, b.str
from TARGET_TAB a, lookup_tab b WHERE a.id = b.id

If “TARGET_TAB” Table doesn’t exist in the database, then the alternative table “ALTERNATIVE_TAB” should be used instead.

select a.id, b.str
from ALTERNATIVE_TAB a, lookup_tab b WHERE a.id = b.id

Requirement : Solve this problem by using a single sql query.

create table TARGET_TAB as select level as id from dual connect by level <4;
create table ALTERNATIVE_TAB as select level as id from dual connect by level <6;
create table lookup_tab as select level as id, chr(64+level) str from dual connect by level <6;

————————————————————-SQL Solution———————————————–

WITH XML AS
(SELECT dbms_xmlgen.getxml('SELECT id FROM '||table_name) xml_clob
 FROM user_tables
 WHERE table_name IN
    (SELECT 'TARGET_TAB' FROM dual
      UNION ALL
      SELECT 'ALTERNATIVE_TAB' FROM dual
      WHERE NOT EXISTS (SELECT NULL FROM user_tables WHERE table_name = 'TARGET_TAB')
     )
 ),
 DATA AS
( SELECT ID
  FROM   XML,  XMLTable('ROWSET/ROW'
                         PASSING XMLTYPE(XML.xml_clob)
                         COLUMNS
                         ID NUMBER PATH 'ID')
 )
 SELECT a.ID, str
 FROM DATA a, lookup_tab b
 WHERE a.ID = b.id;

        ID S
---------- -
         1 A
         2 B
         3 C     

drop table target_tab;

Run the same SQL again.

        ID S
---------- -
         1 A
         2 B
         3 C
         4 D
         5 E