Home » SQL & PL/SQL » SQL & PL/SQL » query related Pivot function of oracle
query related Pivot function of oracle [message #427924] Mon, 26 October 2009 07:01 Go to next message
sohan
Messages: 11
Registered: October 2009
Location: India
Junior Member
how to show matrix of 2 dynamic columns?
Using pivot function it has achieved, but column header do not support more than 30 characters.

eg.

Name xxxyyyyzzzerretqweasdsf123345wertuuu

John checkbox
Mary checkbox
Bill checkbox



The result achieved,

Name xxxyyyyzzzerretqweasdsf123345w

John checkbox
Mary checkbox
Bill checkbox


The result to be achieved,

Name xxxyyyyzzzerretqweasdsf123345wertuuu

John checkbox
Mary checkbox
Bill checkbox
Re: query related Pivot function of oracle [message #427927 is a reply to message #427924] Mon, 26 October 2009 07:45 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Suppress the header and select a row that LOOKS like a header instead.
Use UNION ALL in combination with an ORDER BY to display it as the first row.
Re: query related Pivot function of oracle [message #427932 is a reply to message #427924] Mon, 26 October 2009 08:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SQL>  col dummy format a61 heading "this is a column header that contains more than 30 characters"
SQL> select dummy from dual;
this is a column header that contains more than 30 characters
-------------------------------------------------------------
X

1 row selected.

Regards
Michel

[Updated on: Mon, 26 October 2009 08:02]

Report message to a moderator

Re: query related Pivot function of oracle [message #428092 is a reply to message #427924] Tue, 27 October 2009 04:04 Go to previous messageGo to next message
sohan
Messages: 11
Registered: October 2009
Location: India
Junior Member
Hi Frank,
Can u explain with any example?
Re: query related Pivot function of oracle [message #428097 is a reply to message #428092] Tue, 27 October 2009 04:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Why my answer does not get your agreement?
If you really want a workaround it is:
SQL> set head off
SQL> col dummy format a61 
SQL> col ord noprint
SQL> select 1 ord, 'this is a column header that contains more than 30 characters' dummy from dual
  2  union all
  3  select 2, '-------------------------------------------------------------' from dual
  4  union all
  5  select 3, dummy from dual
  6  order by 1
  7  /
this is a column header that contains more than 30 characters
-------------------------------------------------------------
X

3 rows selected.

which requires an additional sort. (the last one is your actual query.)

Regards
Michel
Re: query related Pivot function of oracle [message #428114 is a reply to message #428097] Tue, 27 October 2009 05:11 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Michels initial solution is the better option, in my view.
I was simply too lazy to check your claim that a header could not exceed 30 characters (which is clearly not true)
Re: query related Pivot function of oracle [message #428131 is a reply to message #428092] Tue, 27 October 2009 05:48 Go to previous messageGo to next message
sohan
Messages: 11
Registered: October 2009
Location: India
Junior Member
Thanks for your immediate reply.

I know that we can format column heading as per our requirement.
But in pivot for clause i am passing dynamic string.
The pivot query is returned by the function and that function is called in report of Oracle apex.

declare

      v_sql   clob;

begin

      select plan_service_matrix.pivot_func(1,2) into v_sql from dual;
       return v_sql;

end; 



This is the code written in report source v_sql contains pivot query .

e.g

Within pivot in clause there is a string ('qwertyuioplkjhgfdsazxcvbnmlkiutr','trial-1',
'qwertyuioplkjhgfdsazxcvbnmlkiuse-2')

Then first and second parameters are same till 30 characters so my report is giving error of "column ambiguously defined".

If parameters are not same then it cuts the heading after 30 characters.

So how to handle this problem?

All the columns are going to be dynamically displayed at runtime.
So how and where to write a command which will format the dynamic column headings as per my requirement.

Thanks

[Updated on: Tue, 27 October 2009 06:13]

Report message to a moderator

Re: query related Pivot function of oracle [message #428164 is a reply to message #428131] Tue, 27 October 2009 06:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You can always named the returned column as you want.
I don't understand your problem.

Put a full working test case we can reproduce and that shows the problem.

Regards
Michel
Re: query related Pivot function of oracle [message #428191 is a reply to message #428164] Tue, 27 October 2009 08:03 Go to previous messageGo to next message
sohan
Messages: 11
Registered: October 2009
Location: India
Junior Member
Thank You so much that you are spending your precious time in solving my problem.Thanx.

Suppose I have 2 tables

1. Name_Info
Name_pk number;
Add_fk number;
Name varchar2(200)

2. Address_Info
Add_pk number;
Add_info varchar2(32767)



Data in Address_Info


Add_pk  Add_Info
  1     qwertyuioplkjhgfdsazxcvbnmloiuyhgt
  2     trial
  3     qwertyuioplkjhgfdsazxcvbnmloivxcxzzasa




Data in Name_Info

Name_pk  Add_fk  Name
  1         1    John
  2         2    Mary
  3         3    Bill



Now I want a matrix with name as row and addresses as column
as described above.

Using pivot query i have achieved it.

Now in Oracle apex in the report region source i am writing the following code.


declare

   v_sql clob;
   v_add clob;

begin

select concatenate_list_with_alias(CURSOR(select add_info from address_info)) into v_add from dual;


v_sql:='select * from (select name,add_info,add_pk from name_info,address_info order by name) pivot (max(replace(add_info,add_info,apex_item.checkbox(10,add_pk))) for add_info in ('||v_add||'))';


return v_sql;

end;



concatenate_list_with_alias :- Gives me comma seperated string as in my example it gives

'qwertyuioplkjhgfdsazxcvbnmloiuyhgt','trial','qwertyuioplkjhgfdsazxcvbnmloivxcxzzasa'

and it is stored in v_add variable.
But as you see that first and third string are same till 30 characters so it gives me error "column ambiguously defined".

If I remove third string and provide the static string in the v_sql query then,

Name    qwertyuioplkjhgfdsazxcvbnmloi  trial

Bill	checkbox                       checkbox
John	checkbox                       checkbox
Mary	checkbox                       checkbox



As you can view that it has truncated the second column name till 30 characters.

How can i solve this problem.

I am not able to understand where to write column format command?

Thanks again..

[Updated on: Tue, 27 October 2009 08:26]

Report message to a moderator

Re: query related Pivot function of oracle [message #428460 is a reply to message #427924] Wed, 28 October 2009 07:27 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
I think, FORMAT command is for SQL *Plus.

Quote:
Now in Oracle apex in the report region source...


and doesn't work with Oracle ApEx.

regards,
Delna
Previous Topic: To Delete 25 lacs Records
Next Topic: Seeing active locks & sessions (translating from Informix) (merged 3)
Goto Forum:
  


Current Time: Mon Feb 17 19:07:57 CST 2025