| dynamic columns for sql [message #497683] |
Sun, 06 March 2011 19:38  |
xpact83
Messages: 225
Registered: October 2008
Location: philippines
|
Senior Member |
|
|
Hi guys! Is there any sql script to generate dynamic columns? pivot still needs the programmer to know the data to generate columns just like this.
select * from
(select current_position, fk_department, wages
from employee)
pivot
(sum(wages)
for fk_department in ('INT', 'WEL', 'CEN', 'POL'));
how about if i don't know the data in fk_department. is this possible in sql?
|
|
|
|
| Re: dynamic columns for sql [message #497704 is a reply to message #497683] |
Sun, 06 March 2011 21:49  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You can create a function that dynamically generates the select statement with the pivot, then select that function from sql, as demonstrated below.
-- test data:
SCOTT@orcl_11gR2> create table employee as
2 select job as current_position,
3 decode
4 (deptno,
5 10, 'INT',
6 20, 'WEL',
7 30, 'CEN',
8 40, 'POL')
9 as fk_department,
10 sal as wages
11 from emp e
12 /
Table created.
SCOTT@orcl_11gR2> select *
2 from (select current_position,
3 fk_department,
4 wages
5 from employee)
6 pivot (sum (wages)
7 for fk_department in
8 ('INT', 'WEL', 'CEN', 'POL'))
9 order by 1
10 /
CURRENT_P 'INT' 'WEL' 'CEN' 'POL'
--------- ---------- ---------- ---------- ----------
ANALYST 6000
CLERK 1300 1900 950
MANAGER 2450 2975 2850
PRESIDENT 5000
SALESMAN 5600
5 rows selected.
-- function that can be called from sql:
SCOTT@orcl_11gR2> create or replace function pivot_func
2 return sys_refcursor
3 as
4 v_sql varchar2 (32767);
5 v_refcur sys_refcursor;
6 begin
7 v_sql :=
8 'select *
9 from (select current_position,
10 fk_department,
11 wages
12 from employee)
13 pivot (sum (wages)
14 for fk_department in (';
15 for r in
16 (select distinct fk_department
17 from employee)
18 loop
19 v_sql := v_sql || '''' || r.fk_department || ''',';
20 end loop;
21 v_sql := rtrim (v_sql, ',') || ')) order by 1';
22 open v_refcur for v_sql;
23 return v_refcur;
24 end pivot_func;
25 /
Function created.
SCOTT@orcl_11gR2> show errors
No errors.
-- select from sql:
SCOTT@orcl_11gR2> select pivot_func from dual
2 /
PIVOT_FUNC
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
CURRENT_P 'WEL' 'CEN' 'INT'
--------- ---------- ---------- ----------
ANALYST 6000
CLERK 1900 950 1300
MANAGER 2975 2850 2450
PRESIDENT 5000
SALESMAN 5600
5 rows selected.
1 row selected.
SCOTT@orcl_11gR2>
|
|
|
|
| Re: dynamic columns for sql [message #497716 is a reply to message #497704] |
Sun, 06 March 2011 23:18 |
xpact83
Messages: 225
Registered: October 2008
Location: philippines
|
Senior Member |
|
|
|
Thanks for the response, I know that it can be done by creating PL/SQL functions or procedures. Is there any work around to have the same result by just using sql alone and not resorting to PL/SQL?
|
|
|
|
| Re: dynamic columns for sql [message #497723 is a reply to message #497716] |
Sun, 06 March 2011 23:47 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
xpact83 wrote on Sun, 06 March 2011 21:18
Is there any work around to have the same result by just using sql alone and not resorting to PL/SQL?
The only other thing you could do is use sql to create sql and dynamically create the select statement by spooling to another file and running that. For example:
store set saved_settings replace
set echo off feedback off heading off pagesize 0 verify off
spool test.sql
prompt select *
prompt from (select current_position,
prompt fk_department,
prompt wages
prompt from employee)
prompt pivot (sum (wages)
prompt for fk_department in (
select wm_concat (fk_department)
from (select distinct '''' || fk_department || '''' fk_department
from employee);
prompt )) order by 1
prompt /
spool off
start saved_settings
start test.sql
[Updated on: Mon, 07 March 2011 00:00] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: dynamic columns for sql [message #497774 is a reply to message #497743] |
Mon, 07 March 2011 02:13 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The way that Oracle implemented it, the in-list has to be a static string. If you are executing from SQL*Plus, then the following is another workaround with a new_value and a substitution variable.
SCOTT@orcl_11gR2> column depts new_value deptlist
SCOTT@orcl_11gR2> select wm_concat (fk_department) depts
2 from (select distinct '''' || fk_department || '''' fk_department
3 from employee)
4 /
DEPTS
--------------------------------------------------------------------------------
'CEN','INT','WEL'
1 row selected.
SCOTT@orcl_11gR2> select *
2 from (select current_position,
3 fk_department,
4 wages
5 from employee)
6 pivot (sum (wages)
7 for fk_department in
8 (&deptlist))
9 order by 1
10 /
old 8: (&deptlist))
new 8: ('CEN','INT','WEL'))
CURRENT_P 'CEN' 'INT' 'WEL'
--------- ---------- ---------- ----------
ANALYST 6000
CLERK 950 1300 1900
MANAGER 2850 2450 2975
PRESIDENT 5000
SALESMAN 5600
5 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
| Re: dynamic columns for sql [message #498186 is a reply to message #497774] |
Wed, 09 March 2011 03:30 |
sethumurugan
Messages: 61
Registered: June 2010
Location: Chennai
|
Member |
|
|
SQL> create or replace function pivot_func return sys_refcursor
2 as
3 v_sql long; --varchar2 (32767);
4 v_refcur sys_refcursor;
5
6 begin
7 v_sql :=
8 'select *
9 from (
10 select employee,
11 mmm,
12 sales_unit
13 from sales_matrix_sethu
14 )
15 pivot
16 (sum (sales_unit)
17 for mmm in (';
18 for r in
19 (select distinct mmm
20 from sales_matrix_sethu)
21 loop
22 v_sql := v_sql || '''' || r.mmm || ''',';
23 end loop;
24 v_sql := rtrim (v_sql, ',') || '))
25 order by 1';
26
27 open v_refcur for v_sql;
28
29 return v_refcur;
30
31 end pivot_func;
32 /
Function created.
SQL> select pivot_func from dual;
select pivot_func from dual
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "EARS.PIVOT_FUNC", line 27
|
|
|
|
| Re: dynamic columns for sql [message #498190 is a reply to message #498186] |
Wed, 09 March 2011 03:46 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Standard method for debugging dynamic sql:
Assign the dyanmic sql string to a varchar variable.
Output varchar variable (use dbms_output)
Copy and paste sql displayed into sqlplus and see what it shows.
|
|
|
|
|
|
| Re: dynamic columns for sql [message #498206 is a reply to message #498200] |
Wed, 09 March 2011 04:21 |
sethumurugan
Messages: 61
Registered: June 2010
Location: Chennai
|
Member |
|
|
Michel. Sorry. I tried to do it. But somehow it did not. I think I had not gone thru the guidelines properly. Am trying again.
Oracle Version is 10.1.0.5.0.
SQL> create or replace function pivot_func return sys_refcursor
2 as
3 v_sql long; --varchar2 (32767);
4 v_refcur sys_refcursor;
5
6 begin
7 v_sql :=
8 'select *
9 from (
10 select employee,
11 mmm,
12 sales_unit
13 from sales_matrix_sethu
14 )
15 pivot
16 (sum (sales_unit)
17 for mmm in (';
18 for r in
19 (select distinct mmm
20 from sales_matrix_sethu)
21 loop
22 v_sql := v_sql || '''' || r.mmm || ''',';
23 end loop;
24 v_sql := rtrim (v_sql, ',') || '))
25 order by 1';
26
27 open v_refcur for v_sql;
28
29 return v_refcur;
30
31 end pivot_func;
32 /
Function created.
SQL> select pivot_func from dual;
select pivot_func from dual
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "EARS.PIVOT_FUNC", line 27
|
|
|
|
| Re: dynamic columns for sql [message #498210 is a reply to message #498190] |
Wed, 09 March 2011 04:25 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
cookiemonster wrote on Wed, 09 March 2011 09:46Standard method for debugging dynamic sql:
Assign the dyanmic sql string to a varchar variable.
Output varchar variable (use dbms_output)
Copy and paste sql displayed into sqlplus and see what it shows.
You've probably got a quote in the wrong place. Doing the above will show you where.
EDIT: typo
[Updated on: Wed, 09 March 2011 04:26] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: dynamic columns for sql [message #498322 is a reply to message #498319] |
Wed, 09 March 2011 10:59 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
In his post, sethumurugan said:
Oracle Version is 10.1.0.5.0.
Also, I copied and pasted the code that he pasted, just did a little formatting and created a sales_matrix_sethu table, and it ran fine in 11g:
SCOTT@orcl_11gR2> select * from v$version
2 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
5 rows selected.
SCOTT@orcl_11gR2> create table sales_matrix_sethu as
2 select ename as employee,
3 job as mmm,
4 sal as sales_unit
5 from emp
6 /
Table created.
SCOTT@orcl_11gR2> create or replace function pivot_func
2 return sys_refcursor
3 as
4 v_sql long; --varchar2 (32767);
5 v_refcur sys_refcursor;
6 begin
7 v_sql :=
8 'select *
9 from (select employee,
10 mmm,
11 sales_unit
12 from sales_matrix_sethu)
13 pivot
14 (sum (sales_unit)
15 for mmm in (';
16 for r in
17 (select distinct mmm
18 from sales_matrix_sethu)
19 loop
20 v_sql := v_sql || '''' || r.mmm || ''',';
21 end loop;
22 v_sql := rtrim (v_sql, ',') || ')) order by 1';
23 open v_refcur for v_sql;
24 return v_refcur;
25 end pivot_func;
26 /
Function created.
SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> select pivot_func from dual
2 /
PIVOT_FUNC
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
EMPLOYEE 'CLERK' 'SALESMAN' 'PRESIDENT' 'MANAGER' 'ANALYST'
---------- ---------- ---------- ----------- ---------- ----------
ADAMS 1100
ALLEN 1600
BLAKE 2850
CLARK 2450
FORD 3000
JAMES 950
JONES 2975
KING 5000
MARTIN 1250
MILLER 1300
SCOTT 3000
SMITH 800
TURNER 1500
WARD 1250
14 rows selected.
1 row selected.
SCOTT@orcl_11gR2>
|
|
|
|
| Re: dynamic columns for sql [message #498435 is a reply to message #498322] |
Thu, 10 March 2011 00:18 |
xpact83
Messages: 225
Registered: October 2008
Location: philippines
|
Senior Member |
|
|
Thanks for the code barbara.
I'm just thinking why is it that oracle did not allow to put subquery in the IN condition of pivot?
And another thing, how about in 10g since there is no pivot what will be your approach in having dynamic columns?
Thanks everyone!
[Updated on: Thu, 10 March 2011 00:31] Report message to a moderator |
|
|
|
| Re: dynamic columns for sql [message #498473 is a reply to message #498435] |
Thu, 10 March 2011 01:46 |
sethumurugan
Messages: 61
Registered: June 2010
Location: Chennai
|
Member |
|
|
Hi Xpact83 and others,
please go thru post Create Dynamic Matrix View which I had posted. In that I had tried to achieve matrix result without using PIVOT option.
When I posted the work I did, Michel Cadot told me that there are some other ways to achive the same. when I searched the forum for PIVOT i got some posts related to that. Based on one post I tried and got stuck as I mentioned above.
So to answer your question xpact83 hope we should follow some other method for PIVOT functionality since this feathre is not available in version less thatn 11g.
Some one please give your comments.
Sethu Murugan
|
|
|
|
|
|
|
|
|
|
|
|
| Re: dynamic columns for sql [message #509282 is a reply to message #497683] |
Fri, 27 May 2011 00:50 |
oralover2006
Messages: 144
Registered: January 2010
Location: India
|
Senior Member |
|
|
dear Barbara Boehmer,
a question regarding your provided solution for 10g Dynamic Pivot, how i can get COLUMN NAMES individually from this ? means if i want only two departments instead of all, but want this procedure as it is to have data for either scenarios.
thanks.
|
|
|
|
|
|
| Re: dynamic columns for sql [message #509292 is a reply to message #509285] |
Fri, 27 May 2011 01:23 |
xpact83
Messages: 225
Registered: October 2008
Location: philippines
|
Senior Member |
|
|
Just a question, the point of making it dynamic is we don't
know what will be the stored data in the department table
if you are going to put where condition for the department column what is the essence of being dynamic?
Can you consider what you just did as spoon feeding michel?
[Updated on: Fri, 27 May 2011 01:26] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: dynamic columns for sql [message #509313 is a reply to message #509282] |
Fri, 27 May 2011 02:23 |
oralover2006
Messages: 144
Registered: January 2010
Location: India
|
Senior Member |
|
|
oralover2006 wrote on Fri, 27 May 2011 11:20dear Barbara Boehmer,
a question regarding your provided solution for 10g Dynamic Pivot, how i can get COLUMN NAMES individually from this ? means if i want only two departments instead of all, but want this procedure as it is to have data for either scenarios.
thanks.
as i mentioned " but want this procedure as it is to have data for either scenarios " means it can process all departments but if i need, for current scenario, two department's data and if i want to extract three or more departments for another scenarios, i can have it from same procedure. OR there is another solution instead REFCURSOR ?
Regards.
[Updated on: Fri, 27 May 2011 02:24] Report message to a moderator |
|
|
|
|
|
| Re: dynamic columns for sql [message #509333 is a reply to message #509319] |
Fri, 27 May 2011 04:33 |
oralover2006
Messages: 144
Registered: January 2010
Location: India
|
Senior Member |
|
|
OK Sir, i am now trying to explain
as Barbara Boehmer did in above two posts
[color=red]->[/color] in first Reply, Barbara created table as below:
SCOTT@orcl_11gR2> create table employee as
2 select job as current_position,
3 decode
4 (deptno,
5 10, 'INT',
6 20, 'WEL',
7 30, 'CEN',
8 40, 'POL')
9 as fk_department,
10 sal as wages
11 from emp e
12 /
Table created.
[color=red]->[/color] now in Reply to teach us [b]10g dynamic pivot[/b]
SCOTT@orcl_11gR2> SELECT * FROM employee
2 /
CURRENT_P FK_ WAGES
--------- --- ----------
CLERK WEL 800
SALESMAN CEN 1600
SALESMAN CEN 1250
MANAGER WEL 2975
SALESMAN CEN 1250
MANAGER CEN 2850
MANAGER INT 2450
ANALYST WEL 3000
PRESIDENT INT 5000
SALESMAN CEN 1500
CLERK WEL 1100
CLERK CEN 950
ANALYST WEL 3000
CLERK INT 1300
14 rows selected.
[color=red]->[/color] created a Procedure to get following results in 10g
CURRENT_P CEN INT WEL
--------- ---------- ---------- ----------
ANALYST 6000
CLERK 950 1300 1900
MANAGER 2850 2450 2975
PRESIDENT 5000
SALESMAN 5600
-> for the final result, i want to select as below
Select * from TABLE ( Function to get )
and
Select current_position, CEN, INT
From TABLE( Function_to_get )
and also
Select current_position, INT, WEL
From TABLE( Function_to_get )
and so on...
hope, this may clear my problem ...
|
|
|
|
|
|
| Re: dynamic columns for sql [message #509359 is a reply to message #509343] |
Fri, 27 May 2011 06:14 |
oralover2006
Messages: 144
Registered: January 2010
Location: India
|
Senior Member |
|
|
Barbara Boehmer wrote on Fri, 27 May 2011 15:43In order to use TABLE(FUNCTION) the function must return a known type with a fixed number of columns, so you could not have a dynamic number of columns.
thanks very much for your important reply Sir, i really learned much from you, always trying to find your valuable posts.
i realized it too, but tried my luck to have any " magic " from Experts, if any.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: dynamic columns for sql [message #516649 is a reply to message #516560] |
Tue, 19 July 2011 20:03 |
xpact83
Messages: 225
Registered: October 2008
Location: philippines
|
Senior Member |
|
|
what I mean is i want to pass the data from the sys_refcursor to a stored procedure. Since the cursor from the function is dynamic how can i fetch its data into variables so that i can process them.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
