Solved

Oracle query to retrive column data into row

Posted on 2011-09-28
13
460 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:toooki
  • 6
  • 3
  • 3
  • +1
13 Comments
 
LVL 7

Expert Comment

by:Jacobfw
Comment Utility
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
 

Author Comment

by:toooki
Comment Utility
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
 
LVL 7

Expert Comment

by:Jacobfw
Comment Utility
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
 

Author Comment

by:toooki
Comment Utility
Thanks.
But I wanted f3, f4 and f5 to be separate columns in the output..
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
I don't think this is possible for an unknown set of columns (rows) even using the new 11gR2 PIVOT SQL function.
0
 
LVL 7

Expert Comment

by:Jacobfw
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:toooki
Comment Utility
Thanks a lot. I am looking into the link example.
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
Comment Utility
>> 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
 

Author Comment

by:toooki
Comment Utility
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
 
LVL 15

Accepted Solution

by:
Devinder Singh Virdi earned 475 total points
Comment Utility
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
 

Author Comment

by:toooki
Comment Utility
Many thanks....
I am trying this.
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
Comment Utility
Little correction
you can turn on heading before calling the file as

spool off;
set head on
@to_execute.sql
exit;
0
 

Author Comment

by:toooki
Comment Utility
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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Creating and Managing Databases with phpMyAdmin in cPanel.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now