Solved

Oracle query to retrive column data into row

Posted on 2011-09-28
13
500 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
  • +1
13 Comments
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36779642
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
ID: 36812708
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
ID: 36814033
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:toooki
ID: 36814827
Thanks.
But I wanted f3, f4 and f5 to be separate columns in the output..
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36815001
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
ID: 36815204
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
 

Author Comment

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

Expert Comment

by:Devinder Singh Virdi
ID: 36920110
>> 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
ID: 36922617
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
ID: 36926500
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
ID: 36934851
Many thanks....
I am trying this.
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 36936106
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
ID: 36937616
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

695 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