Solved

Oracle query to retrive column data into row

Posted on 2011-09-28
13
478 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Exchange 2010 Database wont mount 3 45
Can't Access My Database 57 63
Service Catalogue and Data Migration doc 3 40
Multiple MSSQL instances on same server 4 40
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

786 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