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
Solved

Oracle query to retrive column data into row

Posted on 2011-09-28
13
485 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

789 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