Home » Developer & Programmer » Forms » Rows to Column (Forms 6i)
Rows to Column [message #446369] Mon, 08 March 2010 00:26 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Dear All,

Can anyone explain me how i can convert rows into coulmns as tabular form.

I have two table : om_supplier
supp_code
supp_name

om_supplier_term
st_supp_code
terms


like if i want to make query form from supp_code to supp_code
the output is comming

[code]
select supp_code,terms from om_supplier,om_supplier_term
where supp_Code = st_supp_code
[code]

supp_code terms
1 90days
1 180days
1 120days

i want the output like

supp_code
1 90days-180days-120days

how i can achieve this in form.

Re: Rows to Column [message #446375 is a reply to message #446369] Mon, 08 March 2010 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a PIVOT query asked every week here (especially in SQL & PL/SQL forum).
See http://www.orafaq.com/wiki/SQL_FAQ#How_does_one_code_a_matrix.2Fcrosstab.2Fpivot_report_in_SQL.3F for a first step

Regards
Michel

[Updated on: Mon, 08 March 2010 00:45]

Report message to a moderator

Re: Rows to Column [message #446377 is a reply to message #446375] Mon, 08 March 2010 00:52 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

But muchel in your reference they are talking about numeric values by using decode but i have varchar columns and its not possible by that method as i dont know how many terms are there for one supplier.it may be 3 or 7 also.
Re: Rows to Column [message #446390 is a reply to message #446377] Mon, 08 March 2010 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I have not your tables.
I have not your data.
I don't know from what you start and to what you want to go.

If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Regards
Michel
Re: Rows to Column [message #446446 is a reply to message #446369] Mon, 08 March 2010 07:49 Go to previous messageGo to next message
gregor
Messages: 86
Registered: March 2010
Location: Germany
Member
Hi,

here an way for datastructures, not easy to fetch
in one Query.

you can built datastructures( PL/SQL-Tables) to show what
ever you want,
Writing a Database-Package with Functions for
SELECT , FETCH , LOCK, INSERT , UPDATE, DELETE
and
in your Form.fmb using TRANSACTIONAL-Triggers.
VERY VERY SHORT EXAMPLE:
--------------------------------------
  on-Select :  -- call your Database PAckage PS_SC.read
        ------- EXAMPLE
        ---    IN: QRY_id ;  RETURNING PL/SQLTAB
    	g_erg_last := 0; -- on-Fetch-Counter reset
   p_form.g_tab := PS_SC.READ (pi_QRY_id => 1,
         po_anz_rows  => l_count_rows,
         po_an_column => l_count_column ); 

-----------------------------
on-Fetch:
   WHILE p_form.g_erg_last < p_form.g_tab.COUNT
    LOOP
    p_form.g_erg_counter_last := p_form.g_erg_last + 1;
        CREATE_QUERIED_RECORD;
    -- UNIVERSAL is the name of the FORMS-Block
   :UNIVERSAL.COl1 := p_form.g_tab(p_form.g_erg_last).colu1;
   :UNIVERSAL.COl2 := p_form.g_tab(p_form.g_erg_last).colu2;
   :UNIVERSAL.COl3 ...
     ...
   END LOOP;

---------------------------------
on-insert : on-update
on-delete
write a Database-Package-Procedure, that inserts / updates/ deletes one Record.
---
on-lock :
cursor cu_lock is
  select * from your_tab where id = :universal.id
    for update nowait;

---------------------


( a little bit work, but fully flexible)




CM: Added [code] tags, please do so yourself next time - see the orafaq forum guide if you're not sure how.

[Updated on: Mon, 08 March 2010 08:18] by Moderator

Report message to a moderator

Re: Rows to Column [message #446505 is a reply to message #446446] Mon, 08 March 2010 23:26 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks very much sir, it will appreciate if you give me one sample form with some sample data.
Re: Rows to Column [message #447544 is a reply to message #446505] Mon, 15 March 2010 23:22 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
Have you solved your problem?

David
Re: Rows to Column [message #447553 is a reply to message #447544] Tue, 16 March 2010 00:33 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Martin i just want one sample of how to convert rows to columns in a form.suppose i have one field displaying multiple values , i want to fix these values of rows as columns.
Re: Rows to Column [message #448728 is a reply to message #447553] Thu, 25 March 2010 01:08 Go to previous message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
Read the data in the item. Use PL/SQL functions and logic to parse the data and populate a record in a multi-row non-displayed control block. Then go to your displayed block, populate the record with the data from the first row of the non-display block, do a 'next_record' on each block and repeat.

Alternatively, populate a table in the database, do a 'standard.commit' to save the data, then go to the 'new' block and do an 'execute_query'.

David
Previous Topic: Do Nothing Base on Alert Button Selected
Next Topic: Select a file and load csv data into a table
Goto Forum:
  


Current Time: Fri Sep 20 10:49:35 CDT 2024