Re: How do I construct an select statement to do this view...

From: Evan <mulcaste_at_cadvision.com>
Date: 2000/07/13
Message-ID: <396E27CF.BEEA7A41@cadvision.com>#1/1

Thomas J Kyte posted the following about July 1 in response to a similar question. Seems it might be a starting point for your case too. Build something like the the sample 'select a, get_transposed( a )' into a view. (He also included another solution in his posting, so it might be useful to look up the full text.).

ask_tom_at_OSI1.WORLD> create or replace
  2 function get_transposed( p_a in varchar2 )   3 return varchar2
  4 is

  5      l_str  varchar2(2000) default null;
  6      l_sep  varchar2(1) default null;
  7  begin
  8      for x in ( select b from t where a = p_a ) loop
  9          l_str := l_str || l_sep || x.b;
 10          l_sep := '-';
 11      end loop;
 12      return l_str;

 13 end;
 14 /

Function created.

ask_tom_at_OSI1.WORLD>
ask_tom_at_OSI1.WORLD> select a, get_transposed( a ) t   2 from t
  3 group by a
  4 /

A                         T
------------------------- ------------------------------
210                       5000-5001-5002
220                       6001-6002

Stephen Hurrell wrote:

> Hello.
>
> I have a table#1 that has the following structure with all fields NOT
> NULL.
>
> create table items (
> thekey varchar2(10)
> ,status varchar2(2)
> ,value number(1)
> ) ;
>
> Here is some sample data (the real table has 5-9 million rows..)
>
> 100636 OP 5
> 100636 CL 2
> 100636 ZX 2
> 100636 PL 1
> 123555 OP 4
> 123555 CL 5
> 123555 ZX 1
> 333222 OP 2
> 333222 CL 8
>
> What is the most efficient way to report the data as follows;
>
> thekey thereportstring
> 100636 OP5CL2ZX2PL1
> 123555 CL5OP4ZX1
> 333222 CL8OP2
>
> Here are the requirements and assumptions.
>
> - For any given key there can be 1 or more rows of data.
> - For any given key I want the status with the largest value first in
> field thereportstring down to the smallest value last.
> - The status field can have upto 20 different values and more get added
> to the foreign key.
> - I plan to make this a procedure but pl/sql or sql is OK for now.
> - I will have to make this bi-directional later as I will be getting
> data in both formats but not important now.
>
> Finally I considered having the data table#2 like this, using zero (0)
> for unused values, but it is too sparse, too big and very slow but the
> SQL is trivial to write.
>
> create sparce_items (
> thekey varchar2(10)
> ,status_op number(1) not null
> ,status_cl number(1) not null
> ,status_zx number(1) not null
> ,status_pl number(1) not null
>
> ...and so on for 20 or so different status values....
>
> ) ;
>
> And so I am considering getting to my report from table#1 via a view
> like table#2.
>
> There has to be a better way....
>
> STeve
Received on Thu Jul 13 2000 - 00:00:00 CDT