Oracle query to retrive column data into row

I have Oracle (11gR2) table TEST_trans:

If I query the table:
(
select * from TEST_trans:
f1             f2                            f3                    f4
--------------------------------------------------------
AAA              Lead_of_AAA      10/2/2011      4
AAA              Lead_of_AAA      10/9/2011      5
AAA              Lead_of_AAA      10/16/2011      8
BBB      Lead_of_BBB      10/2/2011      7
BBB      Lead_of_BBB      10/9/2011      0
BBB      Lead_of_BBB      10/16/2011      5
CCC      Lead_of_CCC      10/2/2011      3
CCC      Lead_of_CCC      10/9/2011      1
CCC      Lead_of_CCC      10/16/2011      8
(In the above table for a given f1, f2 is always the same.  Total number of rows in the above table is not known. The dates are repeated for every (f1, f2) values. I know the distinct number of f3 values in the above table. For example above: it is 3. I could get this number by querying another table.

How can I write a query (pl/sql with multiple queries)on the above table that will give an output like:
Here number of columns = (select distinct f3 from TEST_trans) + 2; // +2 is for f1 and f2 fields.
(I know the value (select distinct f3 from TEST_trans)  separately).
f1       f2                        f3    f4  f5
----------------------------------------
AAA   Lead_of_AAA    4     5  8
BBB   Lead_of_BBB  7     0  5
CC    Lead_of_CCC  3     1  8

I have attached the query for table TEST_trans.

Thanks.
CREATE TABLE TEST_trans
( f1 varchar2 (60),
 f2 varchar2 (60),
 f3 date,
 f4 number);
 
INSERT ALL
into TEST_trans (f1, F2, F3, F4) values  ('AAA', 'Lead_of_AAA', to_date('10/2/2011', 'mm/dd/yy'), 4)
into TEST_trans (f1, F2, F3, F4) values  ('AAA', 'Lead_of_AAA', to_date('10/9/2011', 'mm/dd/yy'), 5)
into TEST_trans (f1, F2, F3, F4) values  ('AAA', 'Lead_of_AAA', to_date('10/16/2011', 'mm/dd/yy'), 8)

into TEST_trans (f1, F2, F3, F4) values  ('BBB', 'Lead_of_BBB', to_date('10/2/2011', 'mm/dd/yy'), 7)
into TEST_trans (f1, F2, F3, F4) values  ('BBB', 'Lead_of_BBB', to_date('10/9/2011', 'mm/dd/yy'), 0)
into TEST_trans (f1, F2, F3, F4) values  ('BBB', 'Lead_of_BBB', to_date('10/16/2011', 'mm/dd/yy'), 5)

into TEST_trans (f1, F2, F3, F4) values  ('CCC', 'Lead_of_CCC', to_date('10/2/2011', 'mm/dd/yy'), 3)
into TEST_trans (f1, F2, F3, F4) values  ('CCC', 'Lead_of_CCC', to_date('10/9/2011', 'mm/dd/yy'), 1)
into TEST_trans (f1, F2, F3, F4) values  ('CCC', 'Lead_of_CCC', to_date('10/16/2011', 'mm/dd/yy'), 8)
SELECT * FROM dual;
COMMIT;

Open in new window

toookiAsked:
Who is Participating?
 
Devinder Singh VirdiConnect With a Mentor Lead Oracle DBA TeamCommented:
What about this:-

Create file myqry.sql as below
set echo off
set trim off
set feedback off
set head off
set linesize 1000
set trimspool on
col f1 format a30
col c2 format a30
spool to_execute.sql
select  'select f1, f2, max(' || replace(replace(replace (wm_concat(f3), '''', '"'), ',', ', max('), 'AS', ')AS')  || ' from (' || chr(10) ||
  replace(
  replace(
    'Select f1, f2, decode(f3,' ||
    replace (wm_concat(f3), ',', ', decode(f3,')||
    ', decode' || ' from TEST_TRANS',
  ''' AS "', ''', F4) AS "')
 || ') group by f1, f2;',
   ', decode from', ' from')
from ( select '''' || f3 || ''' AS "' || f3 || '"' as f3, rownum rn from (select distinct f3 from TEST_TRANS order by 1));

spool off;
@to_execute.sql
exit;

and use sqlplus like
sqlplus -s  username/pass@db @mysql.sql

0
 
JacobfwCommented:
This is the standard homework question of transposing rows to columns.
You can do this with a simple list or coma seperated list, but to have dynamic list of columns would be dramatically more complicated.

See: http://www.club-oracle.com/forums/pivoting-row-to-column-conversion-techniques-sql-t144/ 
0
 
toookiAuthor Commented:
Thanks a lot.

I used the example there ...

SELECT f1, f2, LISTAGG(f4, ',') WITHIN GROUP (ORDER BY f3) AS f345
 FROM   test_trans
 GROUP BY f1, f2;

This gives me an output of:
f1            f2                             f345
-------------------------------------------
AAA        Lead_of_AAA      4,5,8
BBB      Lead_of_BBB      7,0,5
CCC      Lead_of_CCC      3,1,8

Is there any way to change the output to:
f1            f2                             f3  f4   f5
-----------------------------------------------
AAA   Lead_of_AAA            4     5   8
BBB   Lead_of_BBB          7     0  5
CC    Lead_of_CCC          3     1  8
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
JacobfwCommented:
SELECT f1, f2, LISTAGG(f4, ',') WITHIN GROUP (ORDER BY f3) AS f345
 FROM   test_trans
 GROUP BY f1, f2;

could be changed to

SELECT f1, f2, LISTAGG(f4, '             ') WITHIN GROUP (ORDER BY f3) AS "f3     f4     f5"
 FROM   test_trans
 GROUP BY f1, f2;

But this is still not dynamic on the column headings (and not really providing dyamic columns, just a third formated column)

0
 
toookiAuthor Commented:
Thanks.
But I wanted f3, f4 and f5 to be separate columns in the output..
0
 
slightwv (䄆 Netminder) Commented:
I don't think this is possible for an unknown set of columns (rows) even using the new 11gR2 PIVOT SQL function.
0
 
JacobfwCommented:
This is the best  example that I have seen to build the SQL statement which can then be run as dynamic SQL.

https://forums.oracle.com/forums/thread.jspa?threadID=1094639
0
 
toookiAuthor Commented:
Thanks a lot. I am looking into the link example.
0
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
>> Total number of rows in the above table is not known
>> I know the distinct number of f3 values in the above table

So we need n-number of columns from that table.
Here is the query, but before that I have one question, are you going to change this query every time or want to declare n-number of columns and is going to use whatever is required?

Method 1:
select f1, f2, max(f_1), max(f_2), max(f_3), max(f_4), max(f_5)
from (
  select f1, f2,
  case when f3=v_f3 and rm = 1 then f4 end f_1,
  case when f3=v_f3 and rm = 2 then f4 end f_2,
  case when f3=v_f3 and rm = 3 then f4 end f_3,
  case when f3=v_f3 and rm = 4 then f4 end f_4, -- no major impact if value is distinct column <= 3
  case when f3=v_f3 and rm = 5 then f4 end f_5 -- no major impact if value is distinct column <= 3
  from  TEST_trans T1, ( select v_f3, rownum rm from (select distinct f3 v_f3 from TEST_trans)) t2
)
group by f1, f2;

Let me think of other possibilities using PL or creation of sql file etc.
0
 
toookiAuthor Commented:
Many thanks for the help.

The number n is known but indirectly when I run the query.
n  = select count(distinct f3) from TEST_trans;

So if the value above is say 4: the desired o/p will look like:

f1            f2                            10/2/2011 10/2/2011   10/2/2011 10/2/2011
--------------------------------------------------------------------------------------------
AAA   Lead_of_AAA            4                  5                 8               10
BBB   Lead_of_BBB          7                   0                5                6
CC    Lead_of_CCC          3                   1                8                 7
(above numbers under the four columns do not match with the table above).

Likewise, if n=3, the desired o/p will be like:
f1            f2                            10/2/2011 10/2/2011   10/2/2011
------------------------------------------------------------------------------
AAA   Lead_of_AAA            4                  5                 8                
BBB   Lead_of_BBB          7                   0                5                
CC    Lead_of_CCC          3                   1                8                

And so on...
The query that you provided provided correct o/p for n<=5 (except that I need the column labels as those date values). Actually typically I expect n to be between 20 and 30...



0
 
toookiAuthor Commented:
Many thanks....
I am trying this.
0
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
Little correction
you can turn on heading before calling the file as

spool off;
set head on
@to_execute.sql
exit;
0
 
toookiAuthor Commented:
virdi_ds, thanks a lot. It somewhat worked for me. However, with everyone's help I understand that it is somewhat very complex to get it done in the database side.

So I tried to use the transpose in the front-end code in asp.net and there I could problematically change the rows to columns based on the need -- and that was complex to but it seems doable.

Many thanks for help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.