Home » SQL & PL/SQL » SQL & PL/SQL » Generate dynamic Unpivot (11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
Generate dynamic Unpivot [message #568709] Tue, 16 October 2012 01:24 Go to next message
knw15pwr
Messages: 122
Registered: March 2010
Senior Member
I want to be able to generate unpivot for 1 row of data dynamically.
Basically what i am trying to do is based on this link

I just need to be able to pass the table name in format : SCHEMA_NAME.TABLE_NAME and the query should unpivot the data for 1 row. (Will always be 1 row, just need to transpose the column names and the values for 1 row of data)

I made the below query so far. But I am getting an error and can't figure out a way to fix it.
Please pass any table name from your database in the define step
define TAB_NAME='SCOTT.EMPLOYEE'
WITH sel_col AS
     (
        SELECT   DECODE (data_type,
                         'NUMBER', 'to_char(' || column_name || ')'||' '||column_name,
                         column_name
                        ) col_list
            FROM all_tab_cols
           WHERE owner LIKE substr('&TAB_NAME',1,instr('&TAB_NAME','.')-1)
             AND table_name LIKE substr('&TAB_NAME',instr('&TAB_NAME','.')+1)
        ORDER BY column_id),
     sel_col_lst AS
     (
        SELECT RTRIM
                  (XMLAGG (XMLELEMENT (e, col_list || ',')).EXTRACT('//text()'),',') colm_name_lst
          FROM sel_col),
     on_col_list AS
     (
        SELECT   RTRIM
                    (XMLAGG (XMLELEMENT (e, column_name || ',')).EXTRACT('//text()'),',') column_name_lst
            FROM all_tab_cols
           WHERE owner LIKE substr('&TAB_NAME',1,instr('&TAB_NAME','.')-1)
             AND table_name LIKE substr('&TAB_NAME',instr('&TAB_NAME','.')+1)
        ORDER BY column_id),
     all_obj_data AS
     (SELECT (SELECT colm_name_lst
                FROM sel_col_list)
        FROM &TAB_NAME
       WHERE ROWNUM < 2)
SELECT column_name, column_value
  FROM all_obj_data
UNPIVOT (column_value
FOR column_name
IN (select column_name_lst from on_col_list))


Error :
ERROR at line 33:
ORA-00904: : invalid identifier


Its the last line of the code. The same seems to work in the select part but not in the IN part.

If there is an even better or elegant solution to this, it will be great to learn.

P.S: I cannot do PL/SQL - as i simply dont have access.
Re: Generate dynamic Unpivot [message #568715 is a reply to message #568709] Tue, 16 October 2012 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 57617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which line is line 33?
Use SQL*Plus and copy and paste your session, the WHOLE session.

Regards
Michel
Re: Generate dynamic Unpivot [message #568716 is a reply to message #568715] Tue, 16 October 2012 01:49 Go to previous messageGo to next message
knw15pwr
Messages: 122
Registered: March 2010
Senior Member
The last line. Sorry.
SQL> ed
Wrote file afiedt.buf

  1  WITH sel_col AS
  2       (
  3          SELECT   DECODE (data_type,
  4                           'NUMBER', 'to_char(' || column_name || ')'||' '||column_name,
  5                           column_name
  6                          ) col_list
  7              FROM all_tab_cols
  8             WHERE owner LIKE substr('&TAB_NAME',1,instr('&TAB_NAME','.')-1)
  9               AND table_name LIKE substr('&TAB_NAME',instr('&TAB_NAME','.')+1)
 10          ORDER BY column_id),
 11       sel_col_lst AS
 12       (
 13          SELECT RTRIM
 14                    (XMLAGG (XMLELEMENT (e, col_list || ',')).EXTRACT('//text()'),',') colm_name_lst
 15            FROM sel_col),
 16       on_col_list AS
 17       (
 18          SELECT   RTRIM
 19                      (XMLAGG (XMLELEMENT (e, column_name || ',')).EXTRACT('//text()'),',') column_name_lst
 20              FROM all_tab_cols
 21             WHERE owner LIKE substr('&TAB_NAME',1,instr('&TAB_NAME','.')-1)
 22               AND table_name LIKE substr('&TAB_NAME',instr('&TAB_NAME','.')+1)
 23          ORDER BY column_id),
 24       all_obj_data AS
 25       (SELECT (SELECT colm_name_lst
 26                  FROM sel_col_list)
 27          FROM &TAB_NAME
 28         WHERE ROWNUM < 2)
 29  SELECT column_name, column_value
 30    FROM all_obj_data
 31  UNPIVOT (column_value
 32  FOR column_name
 33* IN (select column_name_lst from on_col_list))
SQL> /
IN (select column_name_lst from on_col_list))
    *
ERROR at line 33:
ORA-00904: : invalid identifier
Re: Generate dynamic Unpivot [message #568717 is a reply to message #568709] Tue, 16 October 2012 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 57617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> define TAB_NAME='SCOTT.EMP'
SQL> WITH sel_col AS
  2       (
  3          SELECT   DECODE (data_type,
  4                           'NUMBER', 'to_char(' || column_name || ')'||' '||column_name,
  5                           column_name
  6                          ) col_list
  7              FROM all_tab_cols
  8             WHERE owner LIKE substr('&TAB_NAME',1,instr('&TAB_NAME','.')-1)
  9               AND table_name LIKE substr('&TAB_NAME',instr('&TAB_NAME','.')+1)
 10          ORDER BY column_id),
 11       sel_col_lst AS
 12       (
 13          SELECT RTRIM
 14                    (XMLAGG (XMLELEMENT (e, col_list || ',')).EXTRACT('//text()'),',') colm_name_lst
 15            FROM sel_col),
 16       on_col_list AS
 17       (
 18          SELECT   RTRIM
 19                      (XMLAGG (XMLELEMENT (e, column_name || ',')).EXTRACT('//text()'),',') column_name_lst
 20              FROM all_tab_cols
 21             WHERE owner LIKE substr('&TAB_NAME',1,instr('&TAB_NAME','.')-1)
 22               AND table_name LIKE substr('&TAB_NAME',instr('&TAB_NAME','.')+1)
 23          ORDER BY column_id),
 24       all_obj_data AS
 25       (SELECT (SELECT colm_name_lst
 26                  FROM sel_col_list)
 27          FROM &TAB_NAME
 28         WHERE ROWNUM < 2)
 29  SELECT column_name, column_value
 30    FROM all_obj_data
 31  UNPIVOT (column_value
 32  FOR column_name
 33  IN (select column_name_lst from on_col_list))
 34
SQL>
SQL> /
old   8:            WHERE owner LIKE substr('&TAB_NAME',1,instr('&TAB_NAME','.')-1)
new   8:            WHERE owner LIKE substr('SCOTT.EMP',1,instr('SCOTT.EMP','.')-1)
old   9:              AND table_name LIKE substr('&TAB_NAME',instr('&TAB_NAME','.')+1)
new   9:              AND table_name LIKE substr('SCOTT.EMP',instr('SCOTT.EMP','.')+1)
old  21:            WHERE owner LIKE substr('&TAB_NAME',1,instr('&TAB_NAME','.')-1)
new  21:            WHERE owner LIKE substr('SCOTT.EMP',1,instr('SCOTT.EMP','.')-1)
old  22:              AND table_name LIKE substr('&TAB_NAME',instr('&TAB_NAME','.')+1)
new  22:              AND table_name LIKE substr('SCOTT.EMP',instr('SCOTT.EMP','.')+1)
old  27:         FROM &TAB_NAME
new  27:         FROM SCOTT.EMP
IN (select column_name_lst from on_col_list))
    *
ERROR at line 33:
ORA-00904: : invalid identifier


The documentation shows that IN clause is a column or a list of columns.

Regards
Michel
Re: Generate dynamic Unpivot [message #568721 is a reply to message #568717] Tue, 16 October 2012 02:01 Go to previous messageGo to next message
knw15pwr
Messages: 122
Registered: March 2010
Senior Member
Yes I got that.
This select query returns a list of columns.
select column_name_lst from on_col_list


So how do i pass it to the IN clause ?
Re: Generate dynamic Unpivot [message #568726 is a reply to message #568721] Tue, 16 October 2012 02:12 Go to previous messageGo to next message
Michel Cadot
Messages: 57617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
This select query returns a list of columns.


It returns a list of columns but it is not a list of columns.

You can't in SQL.

Regards
Michel
Re: Generate dynamic Unpivot [message #568728 is a reply to message #568726] Tue, 16 October 2012 02:13 Go to previous messageGo to next message
knw15pwr
Messages: 122
Registered: March 2010
Senior Member
Ohh ! Sad
Thanks for the help.
Any other way to do the same in SQL ?
Re: Generate dynamic Unpivot [message #568729 is a reply to message #568728] Tue, 16 October 2012 02:15 Go to previous messageGo to next message
Michel Cadot
Messages: 57617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not in the current versions.
You have to go to PL/SQL.
One example is T.Kyte's print_table function.

Regards
Michel
Re: Generate dynamic Unpivot [message #568731 is a reply to message #568729] Tue, 16 October 2012 02:28 Go to previous messageGo to next message
knw15pwr
Messages: 122
Registered: March 2010
Senior Member
Thanks Michel.
I did have a look at the Print Table function, but as I dont have access to PL/SQL, i was unable to use it and hence was looking for a solution in SQL.
Re: Generate dynamic Unpivot [message #568732 is a reply to message #568731] Tue, 16 October 2012 02:31 Go to previous message
Michel Cadot
Messages: 57617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ah ok, I'm afraid this is not possible... maybe in 12c?

Regards
Michel
Previous Topic: code to load data from text to table ?
Next Topic: Create Record Type with rowtype + one field
Goto Forum:
  


Current Time: Mon Apr 21 10:19:08 CDT 2014

Total time taken to generate the page: 0.05678 seconds