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  |
knw15pwr
Messages: 121
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 #568716 is a reply to message #568715] |
Tue, 16 October 2012 01:49  |
knw15pwr
Messages: 121
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 |
 |
Michel Cadot
Messages: 54236
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
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri May 24 14:32:05 CDT 2013
Total time taken to generate the page: 0.20030 seconds
|
] 
Members
Search
Help
Register
Login
Home
