Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Su.

Asked: January 05, 2005 - 9:25 pm UTC

Last updated: December 01, 2011 - 7:27 am UTC

Version: 9.2.0

Viewed 50K+ times! This question is

You Asked

Hi Tom,

I have the following situation. Please see the SQL's and DDL statements.

The question is: instead of 2 "INSERT INTO...SELECT" statements, is there any way I can do it in one shot?


SQL> CREATE TABLE x (
2 country VARCHAR2(30),
3 state VARCHAR2(10)
4 );

Table created.

SQL>
SQL> INSERT INTO x VALUES ('US', 'CA');

1 row created.

SQL> INSERT INTO x VALUES ('US', 'MA');

1 row created.

SQL> INSERT INTO x VALUES ('US', 'HI');

1 row created.

SQL> INSERT INTO x VALUES ('US', 'TX');

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> CREATE TABLE y (
2 attribute VARCHAR2(30),
3 attribute_value VARCHAR2(30)
4 );

Table created.

SQL> INSERT INTO y
2 SELECT DISTINCT country
3 FROM x;
INSERT INTO y
*
ERROR at line 1:
ORA-00947: not enough values


SQL> INSERT INTO y
2 SELECT DISTINCT 'Country', country
3 FROM x;

1 row created.

SQL> select * from y;

ATTRIBUTE ATTRIBUTE_VALUE
------------------------------ ---------------
Country US

SQL> INSERT INTO y
2 SELECT DISTINCT 'State', state
3 FROM x;

4 rows created.

SQL> commit;

Commit complete.

SQL> select * from y;

ATTRIBUTE ATTRIBUTE_VALUE
------------------------------ ---------------
Country US
State CA
State HI
State MA
State TX


thanks
- Su Baba


and we said...

If state is not UNIQUE, then:

ops$tkyte@ORA9IR2> insert
2 when (rn1=1) then into y (attribute,attribute_value) values( 'Country', country )
3 when (rn2=1) then into y (attribute,attribute_value) values( 'State', state )
4 select country, state,
5 row_number() over (partition by country order by state) rn1,
6 row_number() over (partition by state order by state) rn2
7 from x
8 /

5 rows created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from y;

ATTRIBUTE ATTRIBUTE_VALUE
------------------------------ ------------------------------
Country US
State CA
State HI
State MA
State TX

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> rollback;

Rollback complete.


but if state is in fact unique -- the following would be more efficient


ops$tkyte@ORA9IR2> insert
2 when (rn=1) then into y (attribute,attribute_value) values( 'Country', country )
3 when (rn>0) then into y (attribute,attribute_value) values( 'State', state )
4 select country, state,
5 row_number() over (partition by country order by state) rn
6 from x
7 /

5 rows created.

ops$tkyte@ORA9IR2> select * from y;

ATTRIBUTE ATTRIBUTE_VALUE
------------------------------ ------------------------------
Country US
State CA
State HI
State MA
State TX



Rating

  (55 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Brilliant!

A reader, January 06, 2005 - 9:42 am UTC


Turning Rows Into Columns

A reader, February 05, 2005 - 9:05 pm UTC

Here's what I have:

CREATE TABLE x (
company VARCHAR2(20),
attribute VARCHAR2(20),
attribute_val VARCHAR2(20)
);

INSERT INTO x VALUES ('IBM', 'Country', 'US');
INSERT INTO x VALUES ('IBM', 'State', 'NY');
INSERT INTO x VALUES ('IBM', 'City', 'New York');

INSERT INTO x VALUES ('HP', 'Country', 'US');
INSERT INTO x VALUES ('HP', 'State', 'CA');
INSERT INTO x VALUES ('HP', 'City', 'Palo Alto');

INSERT INTO x VALUES ('Google', 'Country', 'US');
INSERT INTO x VALUES ('Google', 'State', 'CA');
INSERT INTO x VALUES ('Google', 'City', 'Mountain View');

commit;

SELECT * FROM x;

COMPANY ATTRIBUTE ATTRIBUTE_VAL
-------------------- -------------------- -------------
IBM Country US
IBM State NY
IBM City New York
HP Country US
HP State CA
HP City Palo Alto
Google Country US
Google State CA
Google City Mountain View


How do I write a SQL so the above output turned into the following?

IBM US NY New York
HP US CA Palo Alto
Google US CA Mountain View



Tom Kyte
February 06, 2005 - 3:10 am UTC

if you have my book "Effective Oracle by Design", you'll know exactly why

a) i despise this model
b) how to query it (and why it is so hard to query)


select company,
max(decode(attribute, 'Country', attribute_val)),
.... same for other two ATTRIBUTES THAT SHOULD BE ATTRIBUTES, not hidden
.... mystifying data that you cannot query without doing silly thinks like
.... this....
from table
group by company;



Su Baba, March 29, 2005 - 2:14 pm UTC

In reference to the above example, we have a similar situation. In our application, we have the design which captures all customer attributes/attribute value pairs in a table. Let's call this table customer_attributes. A simplified version of the table has the following columns:

CREATE TABLE customer_attributes (
customer_id NUMBER,
attribute_id NUMBER,
attr_text VARCHAR2(500),
attr_value NUMBER
);

An attribute, based on the definition stored in a master table, can be defined to be one of the following data types: string, number, or date. Attributes are stored in customer_attributes in the following way:

data type column
--------- ------------- ----------------------------------------------
string attr_text
date attr_text Stored as a VARCHAR in this format
'yyyymmdd hh24:mi:ss'
number attr_value


Because anyone who uses our application can add any numbers of new attributes to the system, our application cannot make any assumptions regarding the number and the types of attributes that the application will eventually have. As such, it has to be kind of flexible in handling attributes and user queries.

One of the PL/SQL codes that we have is to build a dynamic SQL to handle the question:

Return me all the customers that satisfy the following attribute/value pairs:

e.g.
attribute_id operator value data type
-------------- ----------- --------------------- ----------------------
4 = 'US', 'GB' string
11 = 'ABC Corp%', 'XYZ Inc%' string
21 BETWEEN 1000, 20000000 number
708 > '20020819000000' date

Based on the above input, the PL/SQL code generates the following dynamic SQL. Note that when there are mulitple attribute values within an attribute, it's considered an "OR" condition (e.g. 'US' or 'GB').

select
distinct t.customer_id, c.customer_name, t.rank
from (select party_id, sum(rank) rank
from (select distinct t.customer_id,1 rank
from customer_attributes t
where t.attribute_id = 4 and
attr_text like 'US'
union all
select distinct t.customer_id,1 rank
from customer_attributes t
where t.attribute_id = 4 and
attr_text like 'GB'
union all
select distinct t.customer_id,1 rank
from customer_attributes t
where t.attribute_id = 11 and
attr_text like 'ABC Corp%'
union all
select distinct t.customer_id,1 rank
from customer_attributes t
where t.attribute_id = 11 and
attr_text like 'XYZ Inc%'
union all
select distinct t.customer_id,1 rank
from customer_attributes t
where t.attribute_id = 21 and
attr_value BETWEEN 1000 AND 20000000
union all
select distinct t.customer_id,1 rank
from customer_attributes t
where t.attribute_id = 708 and
attr_text > '20020819000000'
)
group by party_id
) t,
customers c
WHERE t.customer_id = c.customer_id
order by 2 desc;


We're not using bind variables in this SQL. The question I have for this design is: is there any points in rewriting the dynamic SQL so that it will use bind variables? There will be so many different combinations of the SQL that having bind variables probably won't be useful at all?

Can you please advise on the design as well as using the bind variables in this kind of situation? Thanks.



Su Baba, March 29, 2005 - 4:20 pm UTC

We sell our application to customers. Every customer that buys our application will have their own implementation. This means that we need to accomodate for different customer requirements, which entail creation of custom attributes. Because of this, we need to have the flexibility in our application.

I have two separate questions:

(1) What is the proper way to design this kind of application in which attribute sets will be different across customer implmentations?

(2) We always have a common set of attributes which we can define a database column per attribute. How do we deal with the other set of attributes that are not common? Aren't we back to the dynamic SQL option? SHould we be using bind variables in that case?

Tom Kyte
March 29, 2005 - 5:06 pm UTC

1) i'm not fond of any of them, but the use of say "20 varchar2" columns and "20 date" columns and "20 number" columns (20 is number drawn from thin air) to be used is common, allows for indexing and can easily let you create views to facilitate ad-hoc queries

you use vc1..vc20, dt1..dt20, num1..num20 as your "flex fields"

2) dynamic sql is one option, yes and you can certainly (must certainly) bind dynamic sql.

reader

A reader, September 06, 2005 - 2:28 pm UTC

Is there a way to insert into table using
variable with %rowtype


declare
cursor cur1 is select col1, col2 from t1;
var1 cur1%rowtype;
begin
open cur1;
loop
fetch cur1 into var1;
execute immediate('insert into t2 values(var1)');
end loop;
commit;
end;
/

declare
*
ERROR at line 1:
ORA-00947: not enough values
ORA-06512: at line 8


Tom Kyte
September 06, 2005 - 8:54 pm UTC

why are you using dynamic sql??????????


why is this code actually???


insert into t2 (c1,c2) select col1, col2 from t;


that is all this should be, nothing more.

reader

A reader, September 06, 2005 - 10:19 pm UTC

Thanks

This is just an simple example to try a concept of a
more complex situation with tables with many, many columns.
too many to enumerate in the paranthesis.

is there a way to map the values of columns from a
%rowtype variable into a table without listing them in
the paranthesis, may by use of dbms_sql or something

Tom Kyte
September 07, 2005 - 7:32 am UTC

plsql record types - NO

they are known only to plsql. Using STATIC sql, absolutely, 100%.

You cannot dynamically access a record even in PLSQL, let alone SQL.

reader

A reader, September 07, 2005 - 7:54 am UTC

Could you give a simple example on "Using STATIC sql"

Tom Kyte
September 07, 2005 - 8:47 am UTC

Thanks

A reader, September 07, 2005 - 9:06 am UTC


reader

A reader, September 07, 2005 - 11:47 am UTC

declare
cursor cur1 is select col1, col2 from t1;
var1 cur1%rowtype;
begin
open cur1;
loop
fetch cur1 into var1;
insert into t2 values var1;
end loop;
commit;
end;
/

This works. Suppose t2 has one additional column type date
as the first column compared to t1.
t1 is (col1,col2)
t2 is (col0, col1,col2)
is there a way to
insert into t2
<var_date> and the remaining as the record type from t1

Tom Kyte
September 07, 2005 - 1:55 pm UTC

did you read the referenced article?

reader

A reader, September 07, 2005 - 2:18 pm UTC

Yes. Good article on peformance tuning too.

I can create a record type for t1
another record type for t2
assign value of each column from t1.record
to t2.record. It works

I was trying to know if there is a simpler way
to assign values from one record type to another

Tom Kyte
September 07, 2005 - 2:46 pm UTC

why are you creating a new record?? not sure what you are doing there (the reference to the article was to show how to insert using a record that doesn't match the table

insert into ( select c1, c2 from t2 ) values VAR1;

reader

A reader, September 07, 2005 - 3:50 pm UTC

t1 has 2 columns
t2 has 3 columns
I am trying to insert into t2 values of 2 columns from t1
and a addition value sysdate in the third column

I create var1 record type t1 . Get values from t1 into var1
I create var2 record type t2.
Transfer var1 to var2 column by columns <=== is there a simpler way
assign var2.3rd column = sysdate
insert var2 to t2

Tom Kyte
September 07, 2005 - 7:33 pm UTC

so, select sysdate in the query on t1 - t1 now has three columns.


use one thing.

Data Model and Design Questions

A reader, October 18, 2005 - 7:46 pm UTC

I have a few questions in response to your anwser to Su Baba's question
posted on March 29, 2005 regarding custom attributes. You said:

------------------------------------------------------------------------
"1) i'm not fond of any of them, but the use of say "20 varchar2" columns and "20
date" columns and "20 number" columns (20 is number drawn from thin air) to be
used is common, allows for indexing and can easily let you create views to
facilitate ad-hoc queries

you use vc1..vc20, dt1..dt20, num1..num20 as your 'flex fields'"
------------------------------------------------------------------------

1. How does this data model handle attributes with multiple values?
2. Let's say if every attribute is a searchable field. For example,
I may want to ask a question like

"Give me all the customers that are based in US and in California
(that's two separate attributes) and have an annual revenue greater
than $2 million?"

To run a search like this efficiently, I may have to index every
attribute in the table. If I have 100 attributes, I will need to
have at least 100 indexes. Would there be any problem for a table
to have 100 indexes. Is it even advisable to index the table like
that?

3. Let's say some of the attribute sources come from an external
application (within the same database) which my application can
only query but not doing anything else (i.e. no triggers, no
materialized views created on the tables in the external
application). In other words, this portion of my attribute
table is no more than a denormalized table.

In order to populate my attribute table, I'll need to have a
background process that updates my attribute value columns on
a regular basis. Can this update be done in a single UPDATE
statement given that an attribute can possibly have multiple
values (as mentioned in #1 above)?



Tom Kyte
October 19, 2005 - 6:50 am UTC

1) don't know what you mean. This allows an "entity" to have "some flexible attributes". If the entity has a 1:M relationship to something, you need another table (no, not fond of varrays and a nested table is - another table)

2) time to get a data model together in my opinion. "generic" is really "cool". generic is also slow, hard to maintain, inefficient, .........


You would actually need many many many more than 100 indexes - you would not be using bitmap indexes (assuming OLTP here - if not, then maybe you could use bitmaps). So, you would need lots of combinations of concatenated indexes.

this goes back to understanding the data and what questions you shall be asking of the data.

3) I would not call it denormalized necessarily - you are not necessarily repeating the data. Why would you copy it over if you just need to read it? databases were born to join.

A reader, October 20, 2005 - 12:57 am UTC

</code> http://www.oracle.com/technology/oramag/oracle/03-jul/o43asktom.html <code>

see "Insert and Update from a Record"

--------------------------------------------------------

Tom your article does not show how to handlce dup val on index exception when using "Insert and Update from a Record" .

Secondly is this kind of insert and update faster then a regular insert and update using column instead of a record.

Thanks


Tom Kyte
October 20, 2005 - 8:08 am UTC

why would an article on insert/update from a record pick out "dup_val_on_index" in particular??

handling dup_val_on_index is not special or different for this.

This feature of plsql is purely to let you save keystrokes, under the covers, everything is converted into individual column references again.

columns into rows using sql*loader

Thirumaran, October 25, 2005 - 11:17 am UTC

Tom,

I am using Oracle 10g R2. I am working on a prototype.

example:
-----------
Table: codes_t
Type_code varchar2(8) not null,
Code_code varchar2(8) not null,
short_description varchar2(50) not null,
Description varchar2(2000) not null,
Description2 varchar2(2000) ,
Description3 varchar2(2000),
Description4 varchar2(2000),
Description5 varchar2(2000))

Type_code Code_code Short_description Description
--------- --------- ----------------- -----------
COLOR BLUE BLUE COLOR THIS IS .....
COLOR RED RED COLOR SOME TEXT ....
-----------------------------------------------------------
simillarly for Description2 to Description5 the description text is entered.

i am using sql*loader to load data into a normalized table
the table is split into 2 tables (COLUMN based to ROW_based design)

A) codes_t & B) Code_desc_t

Codes_t
-------
PK_code_id number primary key,
Type_code varchar2(8) not null,
Code_code varchar2(8) not null,
short_desc varchar2(50) not null,
Desc varchar2(2000) not null
------------------------------------------------------------
sample data
PK_code_id Type_code Code_code short_desc Desc
---------- --------- --------- ---------- -----
1 COLOR BLUE BLUE_COLOR sometext..
2 COLOR RED RED_COLOR sometext..
------------------------------------------------------------
Code_Desc_T
-----------
PK_desc_id number primary key,
FK_code_id number not null, --(foreign key ref codes_t)
Desc_column varchar2(50) ,
Desc_text varchar2(2000))

sample example Data for this table "code_desc_t" after loading
------------------------------------------------------------
PK_desc_id FK_code_id Desc_column Desc_text
---------- ---------- ------------- ---------
101 1 DESCRIPTION2 Description2 text..
102 1 DESCRIPTION3 Description3 text..
103 1 DESCRIPTION4 Description4 text..
....
------------------------------------------------------------

1) Is it a good design practice to follow column based design ? pro's and cons will be helpfull
2) using sql*loader how can i load data into code_desc_t table as mentioned inthe sample example.
if not possible with sql*loader is ther any other alternate solution
3)How to maintain the foreign key relationship in code_desc_t (fk_code_id)

Thanks In adv
Thirumaran









Tom Kyte
October 26, 2005 - 7:28 am UTC

1) nope, don't like it at all - you should have a table "colors" with red, blue, green. Not a single lookup table for EVERYTHING, but a single lookup table PER THING.

You need to have foreign keys - you won't be able to have foreign keys using a lookup table like this. Very bad practice.

It'll be a mess.

2) simple delimited data - look at ulcase1.ctl in $ORACLE_HOME/rdbms/demo for example.

3) now you understand #1 :) you need a table per lookup, not a single lookup table for everything.




columns into rows using sql*loader

Thirumaran, October 26, 2005 - 11:03 am UTC

Tom,

A) I agree with you on your Design comments
"you should have a table "colors" with red, blue,
green. Not a single lookup table for EVERYTHING, but a single lookup table PER THING."

1) I need to create NEW LOOKUP's dynamically during runtime, so this design was considered. With the splitted table approach "single lookup table PER TABLE" i believe i will not be able to achieve this. Seeking your valuable advice in this regards.

B)As suggested by you
#1) "You need to have foreign keys - you won't be able to have foreign keys using a lookup table like this."
#3)"now you understand #1 :)you need a table per lookup, not a single lookup table for everything."

B1) I am bit confused how to prototype/achive this scenario explained in point (#1)&(#3) above. A Pointer in the right direction with examples will be appreciated.

B2) ref: </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:491314814449 <code>
Quotes from this link "If transactional integrity is meaningful to you across these records, you'll NOT
be using SQLLDR."

please through more light with examples on points B1 & B2 i believe it is contradictory, whether transactional integrity can be achieved using SQLLDR.

Thanks
Thirumaran








Tom Kyte
October 27, 2005 - 3:14 am UTC

a1) why? you do not know your design at development time?

My advice would be to ask "why" and understand if this is really needed.


b) you need to have foreign keys from your tables that reference these lookups. If you have all lookups in a single table - then enforcing RI is very hard, your foreign key would have to be "two" columns - the "name" column AND the id - but the name column would always be constant:

create table t
( ....,
lookup_name varchar2(10) default 'COLOR' check (lookup_name='COLOR'),
lookup_id number,
constraint lookup_fk foreign key(lookup_name,lookup_id) references lookups
)
/


else, you could have a color pointing to a type of banana or something -- it would be "a waste".



I don't know what you mean by with that referenced link? The poster in that link wanted "these N records loaded as a transaction", sqlldr loads records - not "groups of records as a transaction"


Changing columsn to rows

Robin, November 03, 2006 - 3:37 pm UTC

I understand what you're doing here, but can't seem to adapt it to my need. I have a table with two columns, basically listing an school ID (varchar2) and then holiday(date). A school can have as many holidays as it wants (for example, one might take off Columbus day and others won't.

What I'm trying to do is create a view that includes the org's profile data in a record with the org's holiday. For example:

ORGID, ORGNAME, PRINCIPAL, SY_BEGINDATE, HOL, HOL, HOL, HOL, SY_ENDDATE

One school might have 1 holiday while another has 20 so the school with 1 holiday would have NULL underneath the other 19 columns.

Is there any clean, semi-easy way to do this? I've tried using DECODE (which is not pretty because there's not a fixed number of holidays) and UNION ALL. Any help would be greatly appreciated.

Tom Kyte
November 04, 2006 - 12:15 pm UTC

you would have to

a) run a query to get the distinct set of all holiday dates.

b) so you can use those values to create another query


select orgid,
orgname,
principal,
max( decode( datecol, to_date( 'xx-yyy-zzzz' ), 1 ) ) "XX-YYY-ZZZZ",
....
from t
group by orgid, orgname, principal


where xx-yyy-zzzz are your date values retrieved from a)

Not quite sure how...

Robin, November 06, 2006 - 5:35 pm UTC

to get the date values retrieved from a) into query b) without making a join of some sort (which isn't in your query)

Here's a quick example of what I did:

select t.orgid,
max( decode(t.datecol, to_date(a.datecol), 1 ) ) as "XX-YYY-ZZZZ"
t, a
where t.orgid = a.orgid
group by t.orgid

THe results are:

ORGID 'XX-YYY-ZZZZ'
T0001 1
T0002 1
T0003 1




Tom Kyte
November 07, 2006 - 4:27 pm UTC

i do not understand what you are saying.

but really - in order to generate a query with N columns - you need N columns, that is why I suggest a "two step"

one will discover the number of columns
which can be used to build query #2

I'll try to make it clearer

Robin, November 08, 2006 - 4:03 pm UTC

/*

Here's some script to create simple tables to show what I'm trying to do.

What I want to end up with is I want a view that will change the vertical SCHOOL_HOLIDAY table to horizontal rows like this:


ORGID ORGNAME SYBEG SYEND_PROJ HOL
T0001 ORGNAME_001 8/30/2006 6/15/2007 9/4/2006 10/19/2006 10/20/2006
T0008 ORGNAME_008 8/30/2006 6/14/2007 9/4/2006 10/19/2006 10/20/2006
T0014 ORGNAME_014 8/30/2006 6/14/2007 9/4/2006 9/29/2006 10/19/2006 10/20/2006
T0019 ORGNAME_019 8/30/2006 6/13/2007 9/4/2006 10/19/2006 10/20/2006
T0023 ORGNAME_023 8/30/2006 6/14/2007 9/4/2006 10/18/2006 10/19/2006 10/20/2006
T0024 ORGNAME_024 8/30/2006 6/18/2007 9/4/2006 9/29/2006 9/4/2006
T0027 ORGNAME_027 8/30/2006 6/15/2007 9/4/2006 10/18/2006 10/19/2006 10/20/2006 11/10/2006
T0034 ORGNAME_034 8/24/2006 6/13/2007 9/4/2006 10/18/2006 10/19/2006 10/20/2006
T0035 ORGNAME_035 8/30/2006 6/8/2007 9/4/2006 10/9/2006 10/18/2006 10/19/2006 10/20/2006
T0038 ORGNAME_038 8/28/2006 6/12/2007 9/4/2006 10/18/2006 10/19/2006 10/20/2006 11/10/2006
T0042 ORGNAME_042 8/28/2006 6/8/2007 9/4/2006
T0045 ORGNAME_045 8/30/2006 6/8/2007 9/4/2006 9/5/2006 10/5/2006 10/6/2006 10/19/2006 10/20/2006 10/21/2006 10/22/2006 11/10/2006 11/11/2006
T0047 ORGNAME_047 8/30/2006 6/15/2007 9/4/2006 10/19/2006 10/20/2006 11/10/2006

and so on....

I'm sorry I didn't explain it better (it was clear in my head when I was thinking it). Anyway, is this even possible?

*/



CREATE TABLE SCHOOL_PROFILE
(
ORGID VARCHAR2(6 BYTE),
ORGNAME VARCHAR2(50 BYTE),
ORGSTREET_M VARCHAR2(50 BYTE),
ORGTOWN_M VARCHAR2(20 BYTE),
ORGSTATE_M VARCHAR2(2 BYTE),
ORGZIP_M VARCHAR2(11 BYTE),
ORGSTREET_P VARCHAR2(50 BYTE),
ORGTOWN_P VARCHAR2(20 BYTE),
ORGSTATE_P VARCHAR2(2 BYTE),
ORGZIP_P VARCHAR2(11 BYTE),
ORGPHONE VARCHAR2(10 BYTE),
ORGFAX VARCHAR2(10 BYTE),
ORGWEBSITE VARCHAR2(70 BYTE),
FNAME_PR VARCHAR2(20 BYTE),
MNAME_PR VARCHAR2(20 BYTE),
LNAME_PR VARCHAR2(36 BYTE),
POSTNAME_PR VARCHAR2(3 BYTE),
STREET_PR_M VARCHAR2(50 BYTE),
TOWN_PR_M VARCHAR2(20 BYTE),
STATE_PR_M VARCHAR2(2 BYTE),
ZIP_PR_M VARCHAR2(11 BYTE),
STREET_PR_P VARCHAR2(50 BYTE),
TOWN_PR_P VARCHAR2(20 BYTE),
STATE_PR_P VARCHAR2(2 BYTE),
ZIP_PR_P VARCHAR2(11 BYTE),
EM_PR VARCHAR2(50 BYTE),
SYBEG DATE,
SYEND_PROJ DATE
);



Insert into SCHOOL_PROFILE
(ORGID, ORGNAME, ORGSTREET_M, ORGTOWN_M, ORGSTATE_M, ORGZIP_M, ORGSTREET_P, ORGTOWN_P, ORGSTATE_P, ORGZIP_P, ORGPHONE, ORGFAX, ORGWEBSITE, FNAME_PR, MNAME_PR, LNAME_PR, STREET_PR_M, TOWN_PR_M, STATE_PR_M, ZIP_PR_M, STREET_PR_P, TOWN_PR_P, STATE_PR_P, ZIP_PR_P, EM_PR, SYBEG, SYEND_PROJ)
Values
('T0047', 'ORGNAME_047', 'ORGSTREET_M_047', 'ORGTOWN_M_047', 'VT', '05047', 'ORGSTREET_P_047', 'ORGTOWN_P_047', 'VT', '05047', '8025550047', '8025550047', 'WWW.ORGID_T0047.COM', 'JENNIFER', 'KEENAN', 'WOLFE', 'ORGSTREET_M_047', 'ORGTOWN_M_047', 'VT', '05047', 'ORGSTREET_P_047', 'ORGTOWN_P_047', 'VT', '05047', 'PRINCIPAL@ORGID_T0047', TO_DATE('08/30/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/15/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_PROFILE
(ORGID, ORGNAME, ORGSTREET_M, ORGTOWN_M, ORGSTATE_M, ORGZIP_M, ORGSTREET_P, ORGTOWN_P, ORGSTATE_P, ORGZIP_P, ORGPHONE, ORGFAX, ORGWEBSITE, FNAME_PR, LNAME_PR, STREET_PR_M, TOWN_PR_M, STATE_PR_M, ZIP_PR_M, STREET_PR_P, TOWN_PR_P, STATE_PR_P, ZIP_PR_P, EM_PR, SYBEG, SYEND_PROJ)
Values
('T0045', 'ORGNAME_045', 'ORGSTREET_M_045', 'ORGTOWN_M_045', 'VT', '05045', 'ORGSTREET_P_045', 'ORGTOWN_P_045', 'VT', '05045', '8025550045', '8025550045', 'WWW.ORGID_T0045.COM', 'SUSAN', 'BOYER', 'ORGSTREET_M_045', 'ORGTOWN_M_045', 'VT', '05045', 'ORGSTREET_P_045', 'ORGTOWN_P_045', 'VT', '05045', 'PRINCIPAL@ORGID_T0045', TO_DATE('08/31/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/14/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_PROFILE
(ORGID, ORGNAME, ORGSTREET_M, ORGTOWN_M, ORGSTATE_M, ORGZIP_M, ORGSTREET_P, ORGTOWN_P, ORGSTATE_P, ORGZIP_P, ORGPHONE, ORGFAX, ORGWEBSITE, FNAME_PR, LNAME_PR, STREET_PR_M, TOWN_PR_M, STATE_PR_M, ZIP_PR_M, STREET_PR_P, TOWN_PR_P, STATE_PR_P, ZIP_PR_P, EM_PR, SYBEG, SYEND_PROJ)
Values
('T0038', 'ORGNAME_038', 'ORGSTREET_M_038', 'ORGTOWN_M_038', 'VT', '05038', 'ORGSTREET_P_038', 'ORGTOWN_P_038', 'VT', '05038', '8025550038', '8025550038', 'WWW.ORGID_T0038.COM', 'SHERI', 'KINNEY', 'ORGSTREET_M_038', 'ORGTOWN_M_038', 'VT', '05038', 'ORGSTREET_P_038', 'ORGTOWN_P_038', 'VT', '05038', 'PRINCIPAL@ORGID_T0038', TO_DATE('08/28/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/12/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_PROFILE
(ORGID, ORGNAME, ORGSTREET_M, ORGTOWN_M, ORGSTATE_M, ORGZIP_M, ORGSTREET_P, ORGTOWN_P, ORGSTATE_P, ORGZIP_P, ORGPHONE, ORGFAX, ORGWEBSITE, FNAME_PR, LNAME_PR, STREET_PR_M, TOWN_PR_M, STATE_PR_M, ZIP_PR_M, STREET_PR_P, TOWN_PR_P, STATE_PR_P, ZIP_PR_P, EM_PR, SYBEG, SYEND_PROJ)
Values
('T0034', 'ORGNAME_034', 'ORGSTREET_M_034', 'ORGTOWN_M_034', 'VT', '05034', 'ORGSTREET_P_034', 'ORGTOWN_P_034', 'VT', '05034', '8025550034', '8025550034', 'WWW.ORGID_T0034.COM', 'RONALD', 'PAQUETTE', 'ORGSTREET_M_034', 'ORGTOWN_M_034', 'VT', '05034', 'ORGSTREET_P_034', 'ORGTOWN_P_034', 'VT', '05034', 'PRINCIPAL@ORGID_T0034', TO_DATE('08/24/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/13/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_PROFILE
(ORGID, ORGNAME, ORGSTREET_M, ORGTOWN_M, ORGSTATE_M, ORGZIP_M, ORGSTREET_P, ORGTOWN_P, ORGSTATE_P, ORGZIP_P, ORGPHONE, ORGFAX, ORGWEBSITE, FNAME_PR, LNAME_PR, STREET_PR_M, TOWN_PR_M, STATE_PR_M, ZIP_PR_M, STREET_PR_P, TOWN_PR_P, STATE_PR_P, ZIP_PR_P, EM_PR, SYBEG, SYEND_PROJ)
Values
('T0027', 'ORGNAME_027', 'ORGSTREET_M_027', 'ORGTOWN_M_027', 'VT', '05027', 'ORGSTREET_P_027', 'ORGTOWN_P_027', 'VT', '05027', '8025550027', '8025550027', 'WWW.ORGID_T0027.COM', 'KATHY', 'BLANCHARD', 'ORGSTREET_M_027', 'ORGTOWN_M_027', 'VT', '05027', 'ORGSTREET_P_027', 'ORGTOWN_P_027', 'VT', '05027', 'PRINCIPAL@ORGID_T0027', TO_DATE('08/30/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/15/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_PROFILE
(ORGID, ORGNAME, ORGSTREET_M, ORGTOWN_M, ORGSTATE_M, ORGZIP_M, ORGSTREET_P, ORGTOWN_P, ORGSTATE_P, ORGZIP_P, ORGPHONE, ORGFAX, ORGWEBSITE, FNAME_PR, MNAME_PR, LNAME_PR, STREET_PR_M, TOWN_PR_M, STATE_PR_M, ZIP_PR_M, STREET_PR_P, TOWN_PR_P, STATE_PR_P, ZIP_PR_P, EM_PR, SYBEG, SYEND_PROJ)
Values
('T0024', 'ORGNAME_024', 'ORGSTREET_M_024', 'ORGTOWN_M_024', 'VT', '05024', 'ORGSTREET_P_024', 'ORGTOWN_P_024', 'VT', '05024', '8025550024', '8025550024', 'WWW.ORGID_T0024.COM', 'CATHERINE', 'WIEST', 'DESMARAIS', 'ORGSTREET_M_024', 'ORGTOWN_M_024', 'VT', '05024', 'ORGSTREET_P_024', 'ORGTOWN_P_024', 'VT', '05024', 'PRINCIPAL@ORGID_T0024', TO_DATE('08/30/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/18/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_PROFILE
(ORGID, ORGNAME, ORGSTREET_M, ORGTOWN_M, ORGSTATE_M, ORGZIP_M, ORGSTREET_P, ORGTOWN_P, ORGSTATE_P, ORGZIP_P, ORGPHONE, ORGFAX, ORGWEBSITE, FNAME_PR, MNAME_PR, LNAME_PR, STREET_PR_M, TOWN_PR_M, STATE_PR_M, ZIP_PR_M, STREET_PR_P, TOWN_PR_P, STATE_PR_P, ZIP_PR_P, EM_PR, SYBEG, SYEND_PROJ)
Values
('T0023', 'ORGNAME_023', 'ORGSTREET_M_023', 'ORGTOWN_M_023', 'VT', '05023', 'ORGSTREET_P_023', 'ORGTOWN_P_023', 'VT', '05023', '8025550023', '8025550023', 'WWW.ORGID_T0023.COM', 'MICHELLE', 'M', 'SPENCE', 'ORGSTREET_M_023', 'ORGTOWN_M_023', 'VT', '05023', 'ORGSTREET_P_023', 'ORGTOWN_P_023', 'VT', '05023', 'PRINCIPAL@ORGID_T0023', TO_DATE('08/30/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/14/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_PROFILE
(ORGID, ORGNAME, ORGSTREET_M, ORGTOWN_M, ORGSTATE_M, ORGZIP_M, ORGSTREET_P, ORGTOWN_P, ORGSTATE_P, ORGZIP_P, ORGPHONE, ORGFAX, ORGWEBSITE, FNAME_PR, MNAME_PR, LNAME_PR, STREET_PR_M, TOWN_PR_M, STATE_PR_M, ZIP_PR_M, STREET_PR_P, TOWN_PR_P, STATE_PR_P, ZIP_PR_P, EM_PR, SYBEG, SYEND_PROJ)
Values
('T0019', 'ORGNAME_019', 'ORGSTREET_M_019', 'ORGTOWN_M_019', 'VT', '05019', 'ORGSTREET_P_019', 'ORGTOWN_P_019', 'VT', '05019', '8025550019', '8025550019', 'WWW.ORGID_T0019.COM', 'CHRISTOPHER', 'R', 'MASSON', 'ORGSTREET_M_019', 'ORGTOWN_M_019', 'VT', '05019', 'ORGSTREET_P_019', 'ORGTOWN_P_019', 'VT', '05019', 'PRINCIPAL@ORGID_T0019', TO_DATE('08/30/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/13/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_PROFILE
(ORGID, ORGNAME, ORGSTREET_M, ORGTOWN_M, ORGSTATE_M, ORGZIP_M, ORGSTREET_P, ORGTOWN_P, ORGSTATE_P, ORGZIP_P, ORGPHONE, ORGFAX, ORGWEBSITE, FNAME_PR, LNAME_PR, STREET_PR_M, TOWN_PR_M, STATE_PR_M, ZIP_PR_M, STREET_PR_P, TOWN_PR_P, STATE_PR_P, ZIP_PR_P, EM_PR, SYBEG, SYEND_PROJ)
Values
('T0014', 'ORGNAME_014', 'ORGSTREET_M_014', 'ORGTOWN_M_014', 'VT', '05014', 'ORGSTREET_P_014', 'ORGTOWN_P_014', 'VT', '05014', '8025550014', '8025550014', 'WWW.ORGID_T0014.COM', 'ELAINE', 'PICKNEY', 'ORGSTREET_M_014', 'ORGTOWN_M_014', 'VT', '05014', 'ORGSTREET_P_014', 'ORGTOWN_P_014', 'VT', '05014', 'PRINCIPAL@ORGID_T0014', TO_DATE('08/30/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/14/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_PROFILE
(ORGID, ORGNAME, ORGSTREET_M, ORGTOWN_M, ORGSTATE_M, ORGZIP_M, ORGSTREET_P, ORGTOWN_P, ORGSTATE_P, ORGZIP_P, ORGPHONE, ORGFAX, ORGWEBSITE, FNAME_PR, MNAME_PR, LNAME_PR, STREET_PR_M, TOWN_PR_M, STATE_PR_M, ZIP_PR_M, STREET_PR_P, TOWN_PR_P, STATE_PR_P, ZIP_PR_P, EM_PR, SYBEG, SYEND_PROJ)
Values
('T0008', 'ORGNAME_008', 'ORGSTREET_M_008', 'ORGTOWN_M_008', 'VT', '05008', 'ORGSTREET_P_008', 'ORGTOWN_P_008', 'VT', '05008', '8025550008', '8025550008', 'WWW.ORGID_T0008.COM', 'SCOTT', 'V', 'GRAHAM', 'ORGSTREET_M_008', 'ORGTOWN_M_008', 'VT', '05008', 'ORGSTREET_P_008', 'ORGTOWN_P_008', 'VT', '05008', 'PRINCIPAL@ORGID_T0008', TO_DATE('08/30/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/14/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_PROFILE
(ORGID, ORGNAME, ORGSTREET_M, ORGTOWN_M, ORGSTATE_M, ORGZIP_M, ORGSTREET_P, ORGTOWN_P, ORGSTATE_P, ORGZIP_P, ORGPHONE, ORGFAX, ORGWEBSITE, FNAME_PR, LNAME_PR, STREET_PR_M, TOWN_PR_M, STATE_PR_M, ZIP_PR_M, STREET_PR_P, TOWN_PR_P, STATE_PR_P, ZIP_PR_P, EM_PR, SYBEG, SYEND_PROJ)
Values
('T0045', 'ORGNAME_045', 'ORGSTREET_M_045', 'ORGTOWN_M_045', 'VT', '05045', 'ORGSTREET_P_045', 'ORGTOWN_P_045', 'VT', '05045', '8025550045', '8025550045', 'WWW.ORGID_T0045.COM', 'JILL', 'MACKLER', 'ORGSTREET_M_045', 'ORGTOWN_M_045', 'VT', '05045', 'ORGSTREET_P_045', 'ORGTOWN_P_045', 'VT', '05045', 'PRINCIPAL@ORGID_T0045', TO_DATE('08/30/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/08/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_PROFILE
(ORGID, ORGNAME, ORGSTREET_M, ORGTOWN_M, ORGSTATE_M, ORGZIP_M, ORGSTREET_P, ORGTOWN_P, ORGSTATE_P, ORGZIP_P, ORGPHONE, ORGFAX, ORGWEBSITE, FNAME_PR, MNAME_PR, LNAME_PR, STREET_PR_M, TOWN_PR_M, STATE_PR_M, ZIP_PR_M, STREET_PR_P, TOWN_PR_P, STATE_PR_P, ZIP_PR_P, EM_PR, SYBEG, SYEND_PROJ)
Values
('T0042', 'ORGNAME_042', 'ORGSTREET_M_042', 'ORGTOWN_M_042', 'VT', '05042', 'ORGSTREET_P_042', 'ORGTOWN_P_042', 'VT', '05042', '8025550042', '8025550042', 'WWW.ORGID_T0042.COM', 'KELLEY', 'ANN', 'CHERRINGTON', 'ORGSTREET_M_042', 'ORGTOWN_M_042', 'VT', '05042', 'ORGSTREET_P_042', 'ORGTOWN_P_042', 'VT', '05042', 'PRINCIPAL@ORGID_T0042', TO_DATE('08/28/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/08/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_PROFILE
(ORGID, ORGNAME, ORGSTREET_M, ORGTOWN_M, ORGSTATE_M, ORGZIP_M, ORGSTREET_P, ORGTOWN_P, ORGSTATE_P, ORGZIP_P, ORGPHONE, ORGFAX, ORGWEBSITE, FNAME_PR, LNAME_PR, STREET_PR_M, TOWN_PR_M, STATE_PR_M, ZIP_PR_M, STREET_PR_P, TOWN_PR_P, STATE_PR_P, ZIP_PR_P, EM_PR, SYBEG, SYEND_PROJ)
Values
('T0035', 'ORGNAME_035', 'ORGSTREET_M_035', 'ORGTOWN_M_035', 'VT', '05035', 'ORGSTREET_P_035', 'ORGTOWN_P_035', 'VT', '05035', '8025550035', '8025550035', 'WWW.ORGID_T0035.COM', 'JAMES', 'FRAIL', 'ORGSTREET_M_035', 'ORGTOWN_M_035', 'VT', '05035', 'ORGSTREET_P_035', 'ORGTOWN_P_035', 'VT', '05035', 'PRINCIPAL@ORGID_T0035', TO_DATE('08/30/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/08/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_PROFILE
(ORGID, ORGNAME, ORGSTREET_M, ORGTOWN_M, ORGSTATE_M, ORGZIP_M, ORGSTREET_P, ORGTOWN_P, ORGSTATE_P, ORGZIP_P, ORGPHONE, ORGFAX, ORGWEBSITE, FNAME_PR, LNAME_PR, STREET_PR_M, TOWN_PR_M, STATE_PR_M, ZIP_PR_M, STREET_PR_P, TOWN_PR_P, STATE_PR_P, ZIP_PR_P, EM_PR, SYBEG, SYEND_PROJ)
Values
('T0001', 'ORGNAME_001', 'ORGSTREET_M_001', 'ORGTOWN_M_001', 'VT', '05001', 'ORGSTREET_P_001', 'ORGTOWN_P_001', 'VT', '05001', '8025550001', '8025550001', 'WWW.ORGID_T0001.COM', 'ANDREW', 'PACIULLI', 'ORGSTREET_M_001', 'ORGTOWN_M_001', 'VT', '05001', 'ORGSTREET_P_001', 'ORGTOWN_P_001', 'VT', '05001', 'PRINCIPAL@ORGID_T0001', TO_DATE('08/30/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/15/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;


CREATE TABLE SCHOOL_HOLIDAY
(
ORGID VARCHAR2(6 BYTE),
HOL DATE
);

Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0034', TO_DATE('09/04/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0034', TO_DATE('10/18/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0034', TO_DATE('10/19/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0034', TO_DATE('10/20/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0008', TO_DATE('09/04/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0008', TO_DATE('10/19/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0008', TO_DATE('10/20/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0027', TO_DATE('10/18/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0027', TO_DATE('09/04/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0027', TO_DATE('10/19/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0027', TO_DATE('10/20/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0027', TO_DATE('11/10/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0035', TO_DATE('09/04/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0035', TO_DATE('10/09/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0035', TO_DATE('10/18/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0035', TO_DATE('10/19/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0035', TO_DATE('10/20/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0019', TO_DATE('10/19/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0019', TO_DATE('10/20/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0045', TO_DATE('09/04/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0045', TO_DATE('10/06/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0045', TO_DATE('10/19/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0045', TO_DATE('10/20/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0045', TO_DATE('11/10/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0047', TO_DATE('09/04/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0047', TO_DATE('10/19/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0047', TO_DATE('10/20/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0047', TO_DATE('11/10/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0019', TO_DATE('09/04/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0038', TO_DATE('09/04/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0038', TO_DATE('10/18/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0038', TO_DATE('10/19/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0038', TO_DATE('10/20/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0038', TO_DATE('11/10/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0045', TO_DATE('09/04/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0045', TO_DATE('10/19/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0045', TO_DATE('10/20/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0042', TO_DATE('09/04/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0024', TO_DATE('09/04/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0024', TO_DATE('09/29/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0023', TO_DATE('09/04/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0023', TO_DATE('10/18/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0023', TO_DATE('10/19/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0023', TO_DATE('10/20/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0001', TO_DATE('09/04/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0001', TO_DATE('10/19/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0001', TO_DATE('10/20/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0014', TO_DATE('09/04/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0014', TO_DATE('10/19/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0014', TO_DATE('10/20/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SCHOOL_HOLIDAY
(ORGID, HOL)
Values
('T0014', TO_DATE('09/29/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;

-- step a:

CREATE OR REPLACE VIEW query_a
AS
SELECT DISTINCT p.orgid, p.orgname, p.sybeg, p.syend_proj, h.hol
FROM school_profile p, school_holiday h
where p.orgid = h.orgid;


-- step b:

select orgid,
orgname,
sybeg,
syend_proj,
max( decode( hol, to_date( hol), 1 ) ) as "XX-YYY-ZZZZ"
from query_a
group by orgid, orgname, sybeg, syend_proj


/*

ORGID ORGNAME SYBEG SYEND_PROJ XX-YYY-ZZZZ
T0045 ORGNAME_045 8/31/2006 6/14/2007 1
T0014 ORGNAME_014 8/30/2006 6/14/2007 1
T0045 ORGNAME_045 8/30/2006 6/8/2007 1
T0024 ORGNAME_024 8/30/2006 6/18/2007 1
T0023 ORGNAME_023 8/30/2006 6/14/2007 1
T0035 ORGNAME_035 8/30/2006 6/8/2007 1
T0027 ORGNAME_027 8/30/2006 6/15/2007 1
T0001 ORGNAME_001 8/30/2006 6/15/2007 1
T0042 ORGNAME_042 8/28/2006 6/8/2007 1
T0034 ORGNAME_034 8/24/2006 6/13/2007 1
T0047 ORGNAME_047 8/30/2006 6/15/2007 1
T0019 ORGNAME_019 8/30/2006 6/13/2007 1
T0038 ORGNAME_038 8/28/2006 6/12/2007 1
T0008 ORGNAME_008 8/30/2006 6/14/2007 1

/*

I don't believe there's a way to turn columns into rows

Candy, November 13, 2006 - 2:12 pm UTC

I've tried everything in this thread and it doesn't work.

Tom Kyte
November 14, 2006 - 4:11 am UTC

ops$tkyte%ORA9IR2> select * from t;

         A          B
---------- ----------
       100        200

ops$tkyte%ORA9IR2> select decode( r, 1, a, 2 )
  2  from t, (select 1 r from dual union all select 2 r from dual);

DECODE(R,1,A,2)
---------------
            100
              2


??? it is very straight forward. 

I think I get the problem here, but clueless on the solution too.

Bill S., November 14, 2006 - 9:35 am UTC

Tom,

I work with Robin, so I understand the problem she is having. She is kind of puzzled by the responses all through this thread, so she asked me to take a stab at it. Robin has been struggling with this one for a week now, and I haven't been able to give her any help on this one - I just can't get my head around how to make this flat.
The example she gave above is a pretty good representation of the situation, where we have a holiday table that can have anywhere from one to lots of rows per org (two column table, org is varchar2 and holiday is date). Additionally, we have a profile table that contains information pertaining to each org (this is a single row per org). We need to get the org profile information combined with all the holidays for that org into a single outputted row through a query or a view, so that it is flat.
An example would be if org T001 (which has profile information such as street address, city, state, zip, principal (fname, mname, lname, postname), etc) has 3 rows in the holiday table (09/05/2006, 10/11/2006, 11/10/2006) we need to get the profile information and flatten the 3 holiday rows out to combine in a single outputted row (T001, 1 Main Street, Anytown, Yourstate, 05495, John, Q, Principal, , 09/05/2006, 10/11/2006,11/10/2006). This will obviously not output the same length for each org as some orgs will have more holidays than others. It is possible (not likely but possible) that an org may have no holidays.
Not sure if that helps any, but I told her I'd take a stab at explaining it and see if that shook anything loose for you.
Thanks a lot for your time - I know you're busy travelling right now, and we appreciate your taking the time out to help us. If I missed the solution in this thread (or another, we've read so many :-D) I apologize, I'm just not seeing it.

Bill S.

Tom Kyte
November 15, 2006 - 6:31 am UTC

but it is exactly what I said

you need to run a query to figure out the columns you will ultimately have.

then you can write the max(decode()) based on that output.

Hope this helps:

<quote src=expert one on one oracle>

Pivot Query
A pivot query is when you want to take some data such as:

C1        C2      C3 
-----     -----   ------  
a1        b1      x1      
a1        b1      x2      
a1        b1      x3      
…

and you would like to display it as:

C1        C2      C3(1)   C3(2)  C3(3)
-----     -----   ------  -----  ----
a1        b1      x1      x2     x3
…


Basically – turning rows into columns. For example taking the distinct jobs within a department and making them be columns so the output would look like:

DEPTNO     JOB_1     JOB_2     JOB_3
---------- --------- --------- ---------
        10 CLERK     MANAGER   PRESIDENT
        20 ANALYST   ANALYST   CLERK
        30 CLERK     MANAGER   SALESMAN

instead of

DEPTNO     JOB
---------- ---------
        10 CLERK
        10 MANAGER
        10 PRESIDENT
        20 ANALYST
        20 CLERK
        20 MANAGER
        30 CLERK
        30 MANAGER
        30 SALESMAN

I’m going to show two examples for pivots. The first will be another implementation of the preceding question. The second shows how to pivot any result set in a generic fashion and gives you a template for doing so.

In the first case let’s say you wanted to show the top 3 salary earners in each department as COLUMNS. That is – the query would return exactly 1 row per department and the row would have 4 columns – the DEPTNO, the name of the highest paid employee in the department, the name of the next highest paid and so on. Using this new functionality – this is almost easy (before these functions – this was virtually impossible):

ops$tkyte@DEV816> select deptno,
  2             max(decode(seq,1,ename,null)) highest_paid,
  3             max(decode(seq,2,ename,null)) second_highest,
  4             max(decode(seq,3,ename,null)) third_highest
  5    from ( SELECT deptno, ename,
  6                  row_number() OVER
  7                     (PARTITION BY deptno
  8                          ORDER BY sal desc NULLS LAST ) seq
  9             FROM emp )
 10  where seq <= 3
 11  group by deptno
 12  /

    DEPTNO HIGHEST_PA SECOND_HIG THIRD_HIGH
---------- ---------- ---------- ----------
        10 KING       CLARK      MILLER
        20 SCOTT      FORD       JONES
        30 BLAKE      ALLEN      TURNER

That simply created an inner result set that had a sequence assigned to employees BY DEPTNO in order of SAL. The decode in the outer query keeps only rows with sequences 1, 2, or 3 and assigns them to the correct "column". The group by gets rid of the redundant rows and we are left with our collapsed result.  It may be easier to understand what I mean by that if you see the result set without the group by and max:

scott@TKYTE816> select deptno,
  2             (decode(seq,1,ename,null)) highest_paid,
  3             (decode(seq,2,ename,null)) second_highest,
  4             (decode(seq,3,ename,null)) third_highest
  5    from ( SELECT deptno, ename,
  6                  row_number() OVER
  7                     (PARTITION BY deptno
  8                          ORDER BY sal desc NULLS LAST ) seq
  9             FROM emp )
 10  where seq <= 3
 11  /

    DEPTNO HIGHEST_PA SECOND_HIG THIRD_HIGH
---------- ---------- ---------- ----------
        10 KING
        10            CLARK
        10                       MILLER
        20 SCOTT
        20            FORD
        20                       JONES
        30 ALLEN
        30            BLAKE
        30                       MARTIN

9 rows selected.

The MAX aggregate function will be applied by the GROUP BY column DEPTNO.  In any given DEPTNO above only one row will have a non null value for HIGHTEST_PAID, the remaining rows in that group will always be NULL.  The MAX function will pick out the non-null row and keep that for us.  Hence the group by and MAX will “collapse” our result set, removing the NULL values from it and giving us what we want.

In general, if you have a table T with columns C1, C2 and you would like to get a result like:

C1     C2(1)   C2(2)  ….  C2(N)

Where column C1 is to stay "cross record" and column C2 will be pivoted to be "in record" – the values of C2 are to become columns instead of rows – you will generate a query of the form:

Select c1
       max(decode(rn,1,c2,null)) c2_1,
       max(decode(rn,2,c2,null)) c2_2,
       …
       max(decode(rn,N,c2,null)) c2_N
  from ( select c1, c2 
                row_number() over ( partition by C1
                                    order by <something> ) rn
           from T
          <some predicate>
       )
 group by C1 

. In the above example, C1 was simply DEPTNO and C2 was ENAME.  Since we ordered by SAL DESC, the first three columns we retrieved where the top three paid employees in that department (bearing in mind that if four people made the top three, we would of course lose one).

The second example is a more generic "I want to pivot my result set".  Here, instead of having a single column C1 to anchor on and a single column C2 to pivot – we’ll look at the more general case where C1 is a set of columns as is C2.  As it turns out, this is very similar to the above.  Suppose you want to report by JOB and DEPTNO the employees in that job and their salary.  The report needs to have the employees going ACROSS the page as columns however, not down the page – the same with their salaries.  Additionally, the employees need to appear from left to right in order of their salary.  The steps would be:

scott@TKYTE816> select max(count(*)) from emp group by deptno, job;

MAX(COUNT(*))
-------------
            4

That tells us the number of columns, now we can generate the query:

scott@TKYTE816> select deptno, job,
  2         max( decode( rn, 1, ename, null )) ename_1,
  3         max( decode( rn, 1, sal, null )) sal_1,
  4         max( decode( rn, 2, ename, null )) ename_2,
  5         max( decode( rn, 2, sal, null )) sal_2,
  6         max( decode( rn, 3, ename, null )) ename_3,
  7         max( decode( rn, 3, sal, null )) sal_3,
  8         max( decode( rn, 4, ename, null )) ename_4,
  9         max( decode( rn, 4, sal, null )) sal_4
 10    from (  select deptno, job, ename, sal,
 11                   row_number() over ( partition by deptno, job
 12                                           order by sal, ename ) rn
 13              from emp
 14             )
 15  group by deptno, job
 16  /

DEPTNO JOB       ENAME_1 SAL_1 ENAME_2   SAL_2 ENAME_3    SAL_3 ENAME_ SAL_4
------ --------- ------  ----- --------- ----- ---------- ----- ------ -----
    10 CLERK     MILLER   1300
    10 MANAGER   CLARK    2450
    10 PRESIDENT KING     5000
    20 ANALYST   FORD     3000 SCOTT      3000
    20 CLERK     SMITH     800 ADAMS      1100
    20 MANAGER   JONES    2975
    30 CLERK     JAMES      99
    30 MANAGER   BLAKE      99
    30 SALESMAN  ALLEN      99 MARTIN       99 TURNER        99 WARD      99

9 rows selected.

In general, to pivot a result set, we can generalize further.  If you have a set of columns C1, C2, C3, … CN and you want to keep columns C1 .. Cx “cross record” (going down the page) and Cx+1 … CN “in record” (across the page), you can:

Select C1, C2, … CX,
       max(decode(rn,1,C{X+1},null)) cx+1_1,…max(decode(rn,1,CN,null)) CN_1
       max(decode(rn,2,C{X+1},null)) cx+1_2,…max(decode(rn,1,CN,null)) CN_2
       …
       max(decode(rn,N,c{X+1},null)) cx+1_N,…max(decode(rn,1,CN,null)) CN_N
  from ( select C1, C2, … CN
                row_number() over ( partition by C1, C2, … CX
                                    order by <something> ) rn
           from T
          <some predicate>
       )
 group by C1, C2, … CX

In the example, we used C1, C2 = DEPTNO, JOB and C3, C4 = ENAME, SAL

One other thing we must know is the MAXIMUM number of rows per partition we anticipate. This will dictate the number of columns we will be generating. Without it – we cannot pivot. SQL needs to know the number of columns and there is no way around that fact. That leads us into the next more generic  example of pivoting. If we do not know the number of total columns until runtime, we’ll have to use dynamic SQL to deal with the fact that the SELECT list is variable. We can use PL/SQL to demonstrate how to do this – and end up with a generic routine that can be reused whenever you need a pivot. This routine will have the following specification:

scott@TKYTE816> create or replace package my_pkg
  2  as
  3      type refcursor is ref cursor;
  4      type array is table of varchar2(30);
  5
  6      procedure pivot( p_max_cols       in number   default NULL,
  7                       p_max_cols_query in varchar2 default NULL,
  8                       p_query          in varchar2,
  9                       p_anchor         in array,
 10                       p_pivot          in array,
 11                       p_cursor in out refcursor );
 12  end;
 13  /

Package created.

Here, you must send in either P_MAX_COLS or P_MAX_COLS_QUERY.  SQL needs to know the number of columns in a query and this parameter will allow us to build a query with the proper number of columns.  The value you should send in here will be the output of a query similar to:

scott@TKYTE816> select max(count(*)) from emp group by deptno, job;

That is – it is the count of the discrete values that are currently in ROWS that we will put into COLUMNS.  You can either send in the query to get this number, or the number if you already know it.

The P_QUERY parameter is simply the query that gathers your data together.  Using the last example from above the query would be:

 10    from (  select deptno, job, ename, sal,
 11                   row_number() over ( partition by deptno, job
 12                                           order by sal, ename ) rn
 13              from emp
 14             )

The next two inputs are arrays of column names.  The P_ANCHOR tells us what columns will stay CROSS RECORD (down the page) and P_PIVOT states the columns that will go IN RECORD (across the page).  In our example from above, P_ANCHOR = ( ‘DEPTNO’, ‘JOB’ ) and P_PIVOT = (‘ENAME’,’SAL’).  Skipping over the implementation for a moment, the entire call put together might look like this:

scott@TKYTE816> variable x refcursor
scott@TKYTE816> set autoprint on

scott@TKYTE816> begin
  2      my_pkg.pivot
  3      ( p_max_cols_query => 'select max(count(*)) from emp 
                                 group by deptno,job',
  4        p_query => 'select deptno, job, ename, sal,
  5                           row_number() over ( partition by deptno, job
  6                                               order by sal, ename ) rn
  7                      from emp a',
  8        p_anchor => my_pkg.array( 'DEPTNO','JOB' ),
  9        p_pivot  => my_pkg.array( 'ENAME', 'SAL' ),
 10        p_cursor => :x );
 11  end;
 12  /

PL/SQL procedure successfully completed.


DEPTNO JOB       ENAME_ SAL_1 ENAME_2    SAL_2 ENAME_3    SAL_3 ENAME_ SAL_4
------ --------- ------ ----- ---------- ----- ---------- ----- ------ -----
    10 CLERK     MILLER  1300
    10 MANAGER   CLARK   2450
    10 PRESIDENT KING    5000
    20 ANALYST   FORD    3000 SCOTT       3000
    20 CLERK     SMITH    800 ADAMS       1100
    20 MANAGER   JONES   2975
    30 CLERK     JAMES     99
    30 MANAGER   BLAKE     99
    30 SALESMAN  ALLEN     99 MARTIN        99 TURNER        99 WARD      99

9 rows selected.

As you can see – that dynamically rewrote our query using the generalized template we developed.  The implementation of the package body is straightforward:

scott@TKYTE816> create or replace package body my_pkg
  2  as
  3
  4  procedure pivot( p_max_cols          in number   default NULL,
  5                   p_max_cols_query in varchar2 default NULL,
  6                   p_query          in varchar2,
  7                   p_anchor         in array,
  8                   p_pivot          in array,
  9                   p_cursor in out refcursor )
 10  as
 11      l_max_cols number;
 12      l_query    long;
 13      l_cnames   array;
 14  begin
 15      -- figure out the number of columns we must support
 16      -- we either KNOW this or we have a query that can tell us
 17      if ( p_max_cols is not null )
 18      then
 19          l_max_cols := p_max_cols;
 20      elsif ( p_max_cols_query is not null )
 21      then
 22          execute immediate p_max_cols_query into l_max_cols;
 23      else
 24          raise_application_error(-20001, 'Cannot figure out max cols');
 25      end if;
 26
 27
 28      -- Now, construct the query that can answer the question for us...
 29      -- start with the C1, C2, ... CX columns:
 30
 31      l_query := 'select ';
 32      for i in 1 .. p_anchor.count
 33      loop
 34          l_query := l_query || p_anchor(i) || ',';
 35      end loop;
 36
 37      -- Now add in the C{x+1}... CN columns to be pivoted:
 38      -- the format is "max(decode(rn,1,C{X+1},null)) cx+1_1"
 39
 40      for i in 1 .. l_max_cols
 41      loop
 42          for j in 1 .. p_pivot.count
 43          loop
 44              l_query := l_query ||
 45                  'max(decode(rn,'||i||','||
 46                              p_pivot(j)||',null)) ' ||
 47                              p_pivot(j) || '_' || i || ',';
 48          end loop;
 49      end loop;
 50
 51      -- Now just add in the original query
 52      l_query := rtrim(l_query,',')||' from ( '||p_query||') group by ';
 53
 54      -- and then the group by columns...
 55
 56      for i in 1 .. p_anchor.count
 57      loop
 58          l_query := l_query || p_anchor(i) || ',';
 59      end loop;
 60      l_query := rtrim(l_query,',');
 61
 62      -- and return it
 63      execute immediate 'alter session set cursor_sharing=force';
 64      open p_cursor for l_query;
 65      execute immediate 'alter session set cursor_sharing=exact';
 66  end;
 67
 68  end;
 69  /

Package body created.

It only does a little string manipulation to rewrite the query and open a REF CURSOR dynamically.  In the likely event the query had a predicate with constants and such in it, we set cursor sharing on and then back off for the parse of this query to facilitate bind variables (see the section on tuning for more information on that).  Now we have a fully parsed query that is ready to be fetched from.
  
</quote> 

crosstab in Oracle

Keith, November 14, 2006 - 3:15 pm UTC

I think this query gives Robin the results she wants - just extend the "max(decode ..." columns out to 40, rather than 6 and change the seq <= 6 to <= 40.

SELECT orgid, orgname, sybeg, syend_proj,
max(DECODE(seq,1,hol,null)) hol_1,
max(DECODE(seq,2,hol,null)) hol_2,
max(DECODE(seq,3,hol,null)) hol_3,
max(DECODE(seq,4,hol,null)) hol_4,
max(DECODE(seq,5,hol,null)) hol_5,
max(DECODE(seq,6,hol,null)) hol_6
FROM (SELECT orgid, orgname, sybeg, syend_proj, hol,
row_number()
OVER (PARTITION BY orgid
ORDER BY hol desc NULLS LAST) seq
FROM (select sp.orgid, orgname, sybeg, syend_proj, hol from school_profile sp, school_holiday sh where sp.orgid = sh.orgid ))
WHERE seq <= 6
group by orgid, orgname, sybeg, syend_proj;

See </code> http://www.akadia.com/services/ora_analytic_functions.html#Crosstab%20or%20Pivot%20Queries <code>to get an explanation.

oops

Keith, November 14, 2006 - 3:23 pm UTC

Sorry, probably shouldn't have the "desc" in the "OVER(PARTTITION" clause, should be:


OVER (PARTITION BY orgid
ORDER BY hol NULLS LAST) seq


Thank you very much Tom and Keith!

Bill S., November 15, 2006 - 12:06 pm UTC

I think that was just what the doctor ordered (the additional explanation, Tom, and the code example from Keith). We seem to be on the right track now, and Robin and I appreciate the time you guys put in.

This kind of collaboration and information is why I not only come here daily, but also recommend this site to anyone I know of who works with Oracle.

transpose over 200 rows into columns

Nat, December 19, 2006 - 9:00 pm UTC

TOM,
Is it possible to transpose over 200 rows into columns. I have tried with Pivot query, but the data is not coming correctly.

Here is the test data that I am having in a table and trying the data transposed to the dates.
Tried pivot but did not work as the data was not consistent with rundate may or maynot have ertread for some premise.


RUNDATE|ROUTEID|PREMISE|ERTREAD
20040614|03M03T00| 180604|5383
20040614|03M03T00| 180609|9640
20040614|03M03T00| 180614|11217
20040614|03M03T00| 180624|6309
20040614|03M03T00| 180627|7316
20040614|03M03T00| 180630|9035
20040614|03M03T00| 180633|10818
20040614|03M03T00| 180637|8743
20040614|03M03T00| 180640|7945
20040614|03M03T00| 180645|11183
20040614|03M03T00| 180650|10378
20040614|03M03T00| 180655|13525
20040614|03M03T00| 180659|7131
20040615|03M03T00| 180659|7157
20040615|03M03T00| 180604|5403
20040615|03M03T00| 180609|9722
20040615|03M03T00| 180614|11256
20040615|03M03T00| 180624|6335
20040615|03M03T00| 180627|7327
20040615|03M03T00| 180630|9132
20040615|03M03T00| 180633|10827
20040615|03M03T00| 180637|8744
20040615|03M03T00| 180640|8001
20040615|03M03T00| 180645|11230
20040615|03M03T00| 180650|10379
20040615|03M03T00| 180655|13576
20040617|03M03T00| 180604|5495
20040617|03M03T00| 180609|9761
20040617|03M03T00| 180614|11328
20040617|03M03T00| 180624|6373
20040617|03M03T00| 180627|7370
20040617|03M03T00| 180630|9597
20040617|03M03T00| 180633|11070
20040617|03M03T00| 180637|8869
20040617|03M03T00| 180640|8219
20040617|03M03T00| 180645|11588
20040617|03M03T00| 180650|10582
20040617|03M03T00| 180655|13961
20040617|03M03T00| 180659|7450
20040618|03M03T00| 180659|7467
20040618|03M03T00| 180604|5526
20040618|03M03T00| 180609|9905
20040618|03M03T00| 180614|11350
20040618|03M03T00| 180624|6486
20040618|03M03T00| 180627|7375
20040618|03M03T00| 180630|9643
20040618|03M03T00| 180633|11118
20040618|03M03T00| 180637|9018
20040618|03M03T00| 180640|8240
20040618|03M03T00| 180645|11635
20040618|03M03T00| 180650|10582
20040618|03M03T00| 180655|13994
20040619|03M03T00| 180604|5531
20040619|03M03T00| 180609|9937
20040619|03M03T00| 180614|11393
20040619|03M03T00| 180624|6491
20040619|03M03T00| 180627|7513
20040619|03M03T00| 180630|9675
20040619|03M03T00| 180633|11175
20040619|03M03T00| 180637|9179
20040619|03M03T00| 180640|8280
20040619|03M03T00| 180645|11682
20040619|03M03T00| 180650|10682
20040619|03M03T00| 180655|14269
20040619|03M03T00| 180659|7656
20040620|03M03T00| 180604|5610
20040620|03M03T00| 180609|10229
20040620|03M03T00| 180614|11744
20040620|03M03T00| 180624|6685
20040620|03M03T00| 180627|7697
20040620|03M03T00| 180630|9739
20040620|03M03T00| 180633|11271
20040620|03M03T00| 180637|9407
20040620|03M03T00| 180640|8521
20040620|03M03T00| 180645|12007
20040620|03M03T00| 180650|10984
20040620|03M03T00| 180655|14457
20040620|03M03T00| 180659|7802
20040621|03M03T00| 180604|5665
20040621|03M03T00| 180609|10307
20040621|03M03T00| 180614|11918
20040621|03M03T00| 180624|6804
20040621|03M03T00| 180627|7849
20040621|03M03T00| 180630|9782
20040621|03M03T00| 180633|11335
20040621|03M03T00| 180637|9413
20040621|03M03T00| 180640|8666
20040621|03M03T00| 180645|12059
20040621|03M03T00| 180650|10984
20040621|03M03T00| 180655|14524
20040621|03M03T00| 180659|7817
20040622|03M03T00| 180604|5768
20040622|03M03T00| 180609|10348
20040622|03M03T00| 180614|11984
20040622|03M03T00| 180624|6885
20040622|03M03T00| 180627|7850
20040622|03M03T00| 180630|9817
20040622|03M03T00| 180633|11389
20040622|03M03T00| 180637|9432
20040622|03M03T00| 180640|8719
20040622|03M03T00| 180645|12150
20040622|03M03T00| 180650|11002
20040622|03M03T00| 180655|14587
20040622|03M03T00| 180659|7839
20040623|03M03T00| 180604|5872
20040623|03M03T00| 180609|10375
20040623|03M03T00| 180614|12114
20040623|03M03T00| 180624|7036
20040623|03M03T00| 180627|7850
20040623|03M03T00| 180630|10028
20040623|03M03T00| 180633|11410
20040623|03M03T00| 180637|9472
20040623|03M03T00| 180640|8858
20040623|03M03T00| 180645|12310
20040623|03M03T00| 180650|11046
20040623|03M03T00| 180655|14853
20040623|03M03T00| 180659|7888
20040624|03M03T00| 180604|6043
20040624|03M03T00| 180609|10600
20040624|03M03T00| 180614|12471
20040624|03M03T00| 180624|7100
20040624|03M03T00| 180627|7850
20040624|03M03T00| 180630|10092
20040624|03M03T00| 180633|11415
20040624|03M03T00| 180637|9671
20040624|03M03T00| 180640|9100
20040624|03M03T00| 180645|12691
20040624|03M03T00| 180650|11090
20040624|03M03T00| 180655|14988
20040624|03M03T00| 180659|7961
20040625|03M03T00| 180659|8002
20040625|03M03T00| 180604|6065
20040625|03M03T00| 180609|10685
20040625|03M03T00| 180614|12537
20040625|03M03T00| 180624|7303
20040625|03M03T00| 180627|7850
20040625|03M03T00| 180630|10164
20040625|03M03T00| 180633|11466
20040625|03M03T00| 180637|10138
20040625|03M03T00| 180640|9282
20040625|03M03T00| 180645|12908
20040625|03M03T00| 180650|11106
20040625|03M03T00| 180655|15061
20040626|03M03T00| 180604|6093
20040626|03M03T00| 180609|10713
20040626|03M03T00| 180614|12664
20040626|03M03T00| 180624|7420
20040626|03M03T00| 180627|7850
20040626|03M03T00| 180630|10198
20040626|03M03T00| 180633|11495
20040626|03M03T00| 180637|10149
20040626|03M03T00| 180640|9299
20040626|03M03T00| 180645|12959
20040626|03M03T00| 180650|11123
20040626|03M03T00| 180655|15290
20040626|03M03T00| 180659|8098
20040627|03M03T00| 180604|6262
20040627|03M03T00| 180609|10838
20040627|03M03T00| 180614|13000
20040627|03M03T00| 180624|7469
20040627|03M03T00| 180627|7850
20040627|03M03T00| 180630|10220
20040627|03M03T00| 180633|11531
20040627|03M03T00| 180637|10153
20040627|03M03T00| 180640|9477
20040627|03M03T00| 180645|13346
20040627|03M03T00| 180650|11215
20040627|03M03T00| 180655|15612
20040627|03M03T00| 180659|8214
20040628|03M03T00| 180604|6370
20040628|03M03T00| 180609|10982
20040628|03M03T00| 180614|13206
20040628|03M03T00| 180624|7655
20040628|03M03T00| 180627|7850
20040628|03M03T00| 180630|10328
20040628|03M03T00| 180633|11605
20040628|03M03T00| 180637|10527
20040628|03M03T00| 180640|9672
20040628|03M03T00| 180645|13402
20040628|03M03T00| 180650|11231
20040628|03M03T00| 180655|15790
20040628|03M03T00| 180659|8269
20040629|03M03T00| 180604|6378
20040629|03M03T00| 180609|11013
20040629|03M03T00| 180614|13225
20040629|03M03T00| 180624|7671
20040629|03M03T00| 180627|7875
20040629|03M03T00| 180630|10365
20040629|03M03T00| 180633|11620
20040629|03M03T00| 180637|10667
20040629|03M03T00| 180640|9750
20040629|03M03T00| 180645|13451
20040629|03M03T00| 180650|11253
20040629|03M03T00| 180655|15852
20040629|03M03T00| 180659|8308
20040630|03M03T00| 180604|6417
20040630|03M03T00| 180609|11037
20040630|03M03T00| 180614|13240
20040630|03M03T00| 180624|7680
20040630|03M03T00| 180627|7939
20040630|03M03T00| 180630|10407
20040630|03M03T00| 180633|11629
20040630|03M03T00| 180637|10726
20040630|03M03T00| 180640|9829
20040630|03M03T00| 180645|13501
20040630|03M03T00| 180650|11313
20040630|03M03T00| 180655|16226
20040630|03M03T00| 180659|8399
20040701|03M03T00| 180604|6519
20040701|03M03T00| 180609|11109
20040701|03M03T00| 180614|13611
20040701|03M03T00| 180624|7899
20040701|03M03T00| 180627|7960
20040701|03M03T00| 180630|10447
20040701|03M03T00| 180633|11724
20040701|03M03T00| 180637|10796
20040701|03M03T00| 180640|9996
20040701|03M03T00| 180645|13621
20040701|03M03T00| 180650|11330
20040701|03M03T00| 180655|16267
20040701|03M03T00| 180659|8557
20040702|03M03T00| 180604|6533
20040702|03M03T00| 180609|11131
20040702|03M03T00| 180614|13632
20040702|03M03T00| 180624|7913
20040702|03M03T00| 180627|8039
20040702|03M03T00| 180630|10480
20040702|03M03T00| 180633|11747
20040702|03M03T00| 180637|10885
20040702|03M03T00| 180640|10001
20040702|03M03T00| 180645|13666
20040702|03M03T00| 180650|11347
20040702|03M03T00| 180655|16301
20040702|03M03T00| 180659|8579
20040703|03M03T00| 180604|6571
20040703|03M03T00| 180609|11170
20040703|03M03T00| 180614|13763
20040703|03M03T00| 180624|7927
20040703|03M03T00| 180627|8152
20040703|03M03T00| 180630|10505
20040703|03M03T00| 180633|11768
20040703|03M03T00| 180637|10963
20040703|03M03T00| 180640|10001
20040703|03M03T00| 180645|13711
20040703|03M03T00| 180650|11446
20040703|03M03T00| 180655|16677
20040703|03M03T00| 180659|8723
20040704|03M03T00| 180604|6703
20040704|03M03T00| 180609|11310
20040704|03M03T00| 180614|14068
20040704|03M03T00| 180624|8123
20040704|03M03T00| 180627|8433
20040704|03M03T00| 180630|10576
20040704|03M03T00| 180633|11850
20040704|03M03T00| 180637|11062
20040704|03M03T00| 180640|10158
20040704|03M03T00| 180645|13720
20040704|03M03T00| 180650|11481
20040704|03M03T00| 180655|16748
20040704|03M03T00| 180659|8781
20040705|03M03T00| 180604|6705
20040705|03M03T00| 180609|11492
20040705|03M03T00| 180614|14161
20040705|03M03T00| 180624|8218
20040705|03M03T00| 180627|8561
20040705|03M03T00| 180630|10603
20040705|03M03T00| 180659|8811
20040705|03M03T00| 180633|11880
20040705|03M03T00| 180637|11257
20040705|03M03T00| 180640|10160
20040705|03M03T00| 180645|13765
20040705|03M03T00| 180650|11514
20040705|03M03T00| 180655|16845
20040706|03M03T00| 180659|8935
20040706|03M03T00| 180604|6726
20040706|03M03T00| 180609|11682
20040706|03M03T00| 180614|14215
20040706|03M03T00| 180624|8231
20040706|03M03T00| 180627|8639
20040706|03M03T00| 180630|10625
20040706|03M03T00| 180633|11896
20040706|03M03T00| 180637|11291
20040706|03M03T00| 180640|10170
20040706|03M03T00| 180645|13815
20040706|03M03T00| 180650|11548
20040706|03M03T00| 180655|16960
20040707|03M03T00| 180604|6766
20040707|03M03T00| 180609|11717
20040707|03M03T00| 180614|14273
20040707|03M03T00| 180624|8293
20040707|03M03T00| 180627|8693
20040707|03M03T00| 180630|10677
20040707|03M03T00| 180633|11909
20040707|03M03T00| 180637|11356
20040707|03M03T00| 180640|10178
20040707|03M03T00| 180645|13945
20040707|03M03T00| 180650|11578
20040707|03M03T00| 180655|17272
20040707|03M03T00| 180659|9074
20040709|03M03T00| 180604|6963
20040709|03M03T00| 180609|12206
20040709|03M03T00| 180614|14682
20040709|03M03T00| 180624|8415
20040709|03M03T00| 180627|8878
20040709|03M03T00| 180630|10853
20040709|03M03T00| 180633|12076
20040709|03M03T00| 180637|11712
20040709|03M03T00| 180640|10285
20040709|03M03T00| 180645|14516
20040709|03M03T00| 180650|11694
20040709|03M03T00| 180655|17600
20040709|03M03T00| 180659|9214
20040710|03M03T00| 180604|7054
20040710|03M03T00| 180609|12217
20040710|03M03T00| 180614|14724
20040710|03M03T00| 180624|8426
20040710|03M03T00| 180627|8911
20040710|03M03T00| 180630|10905
20040710|03M03T00| 180633|12135
20040710|03M03T00| 180637|11715
20040710|03M03T00| 180640|10322
20040710|03M03T00| 180645|14588
20040710|03M03T00| 180650|11728
20040710|03M03T00| 180655|17992
20040710|03M03T00| 180659|9381
20040711|03M03T00| 180604|7200
20040711|03M03T00| 180609|12232
20040711|03M03T00| 180614|14879
20040711|03M03T00| 180624|8536
20040711|03M03T00| 180627|8947
20040711|03M03T00| 180630|10955
20040711|03M03T00| 180633|12156
20040711|03M03T00| 180637|11715
20040711|03M03T00| 180640|10520
20040711|03M03T00| 180645|15149
20040711|03M03T00| 180650|11816
20040711|03M03T00| 180655|18261
20040711|03M03T00| 180659|9405
20040712|03M03T00| 180604|7312
20040712|03M03T00| 180609|12235
20040712|03M03T00| 180614|14989
20040712|03M03T00| 180624|8682
20040712|03M03T00| 180627|9235
20040712|03M03T00| 180630|11102
20040712|03M03T00| 180633|12185
20040712|03M03T00| 180637|12048
20040712|03M03T00| 180640|10713
20040712|03M03T00| 180645|15541
20040712|03M03T00| 180650|11892
20040712|03M03T00| 180655|18316
20040712|03M03T00| 180659|9427
20040713|03M03T00| 180604|7326
20040713|03M03T00| 180609|12242
20040713|03M03T00| 180614|15063
20040713|03M03T00| 180624|8742
20040713|03M03T00| 180627|9252
20040713|03M03T00| 180630|11137
20040713|03M03T00| 180633|12215
20040713|03M03T00| 180637|12079
20040713|03M03T00| 180640|10752
20040713|03M03T00| 180645|15612
20040713|03M03T00| 180650|11945
20040713|03M03T00| 180655|18390
20040713|03M03T00| 180659|9534
20040714|03M03T00| 180604|7488
20040714|03M03T00| 180609|12245
20040714|03M03T00| 180614|15105
20040714|03M03T00| 180624|8775
20040714|03M03T00| 180627|9280
20040714|03M03T00| 180630|11308
20040714|03M03T00| 180633|12287
20040714|03M03T00| 180637|12117
20040714|03M03T00| 180640|10779
20040714|03M03T00| 180645|15668
20040714|03M03T00| 180650|12040
20040714|03M03T00| 180655|18752
20040714|03M03T00| 180659|9555
20040715|03M03T00| 180604|7552
20040715|03M03T00| 180609|12421
20040715|03M03T00| 180614|15355
20040715|03M03T00| 180624|8894
20040715|03M03T00| 180627|9693
20040715|03M03T00| 180630|11440
20040715|03M03T00| 180633|12332
20040715|03M03T00| 180637|12165
20040715|03M03T00| 180640|11053
20040715|03M03T00| 180645|16174
20040715|03M03T00| 180650|12053
20040715|03M03T00| 180655|18796
20040715|03M03T00| 180659|9716


The data I am looking for should be transposed based on PREMISE and RUNDATE.

PREMISE ROUTEID 20040614 20040615 20040616 20040617 20040618 .......................
180604 03M03T00 5383 5403 - 5495 5526 .........
180609 03M03T00 9640 9722 - 9761 9905 ....
180614 03M03T00 11217 11256 - 11328 11350 ....
.....
....
....

Here is what I have, but while executing I found that after 18 iterations building up to 18 rundate columns
(while checking in debug mode), the l_stmt fails saying 'undeclared identifier "l_stmt"'.
Is there any other way to generate if I have more number of columns to generate.



CREATE OR REPLACE package pivot
as
type rc is ref cursor;
procedure data ( p_cursor in out rc );
end;
/



create or replace package body pivot as
procedure data(p_cursor in out rc )
is
l_stmt long;
begin


l_stmt := 'select routeid,premise ';
for x in ( select distinct rundate from testread order by 1 )
loop
l_stmt := l_stmt ||
', max(decode(rundate,' || x.rundate ||
', ertread )) ' || x.rundate;
end loop;
l_stmt := l_stmt || ' from testread group by routeid,premise order by premise';


open p_cursor for l_stmt;
end;

end;
/


Any help is appreciated. Thanks.


Tom Kyte
December 20, 2006 - 8:06 am UTC

why don't you

a) provide a create table
b) A COUPLE (FEW) inserts into it
c) a static SQL query that you think should work

and we'll debug it from there.

just saying "it doesn't work" is not very useful

More turning COLUMNS into ROWS...

Robert, December 20, 2006 - 5:58 pm UTC

Tom,

I have a tricky turning "columns into rows" predicament...

Here are the steps to set up my problem...

----------------------------
-- TARGET table to store list of account_ids
----------------------------
create table demo_account_list
(
account_id number
)
/

----------------------------
-- SOURCE table of account_ids
----------------------------
create table demo_account_sources
(
acct_1 number
, acct_2 number
, acct_3 number
, acct_4 number
)
/

----------------------------
-- Populate the account_id SOURCE table
----------------------------
insert into demo_account_sources values (1,2,3,4);
insert into demo_account_sources values (13,22,433,44261);
insert into demo_account_sources values (10,342,32342,33443);
insert into demo_account_sources values (15,26,737,48);

commit;

------------------------------
-- Want to capture only the EVEN account_ids
-- This doesn't work!
------------------------------
insert into demo_account_list
(
select acct_1, acct_2, acct_3, acct_4
from demo_account_sources
-----------------------------
-- Want to load only EVEN account_ids
-----------------------------
where mod(acct_1,2) = 0
or mod(acct_2,2) = 0
or mod(acct_3,2) = 0
or mod(acct_4,2) = 0
)
/

----------------------------
Of course, the above query returns this error...
----------------------------
insert into demo_account_list
*
ERROR at line 1:
ORA-00913: too many values
----------------------------

My question is, how can I write this query in such a way that I can select >1 rows yet insert into a single column?

Thanks,

Robert


Tom Kyte
December 20, 2006 - 7:18 pm UTC

ops$tkyte%ORA10GR2> select decode( r, 1, acct_1, 2, acct_2, 3, acct_3, 4, acct_4 ) acct
  2    from demo_account_sources,
  3         (select 1 r from dual union all select 2 from dual
  4          union all select 3 from dual union all select 4 from dual )
  5  /

      ACCT
----------
         1
        13
        10
        15
         2
        22
       342
        26
         3
       433
     32342
       737
         4
     44261
     33443
        48

16 rows selected.


does that help you get started... 

That's it!!!!

Robert, December 20, 2006 - 7:25 pm UTC

Tom,

That is exactly what I was looking for!
(I just now saw your post from 'November 13, 2006' in this same thread where you describe the same solution).

For the record, my purpose for this is that I have to scan a very large table which contains several 'account_id' columns and I only want to make a single pass through it instead of one pass for each 'account_id' column.

Thanks!

Robert.

Nat, December 21, 2006 - 5:31 pm UTC

Tom,
Here is the test scenario that you have asked for. I compiled and ran in debug mode.
After generating about 15 days of rundate in the l_stmt in the loop,
it gave an undeclared identifier 'l_stmt' error.

create table testdata (rundate varchar2(8),
routeid varchar2(8),
premise varchar2(8),
ertread number);




select routeid,premise,max(decode(rundate,'20040614',ertread)) "20040614",
max(decode(rundate,'20040615',ertread)) "20040615",max(decode(rundate,'20040616',ertread)) "20040616",
max(decode(rundate,'20040617',ertread)) "20040617",max(decode(rundate,'20040618',ertread)) "20040618",
max(decode(rundate,'20040619',ertread)) "20040619",max(decode(rundate,'20040620',ertread)) "20040620"
from testdata where premise between 180600 and 180660 and rundate between '20040614' and '20040620'
group by routeid,premise;


The results are piped

ROUTEID|PREMISE|20040614|20040615|20040616|20040617|20040618|20040619|20040620
03M03T00| 180604|5383|5403||5495|5526|5531|5610
03M03T00| 180609|9640|9722||9761|9905|9937|10229
03M03T00| 180614|11217|11256||11328|11350|11393|11744
03M03T00| 180624|6309|6335||6373|6486|6491|6685
03M03T00| 180627|7316|7327||7370|7375|7513|7697
03M03T00| 180630|9035|9132||9597|9643|9675|9739
03M03T00| 180633|10818|10827||11070|11118|11175|11271
03M03T00| 180637|8743|8744||8869|9018|9179|9407
03M03T00| 180640|7945|8001||8219|8240|8280|8521
03M03T00| 180645|11183|11230||11588|11635|11682|12007
03M03T00| 180650|10378|10379||10582|10582|10682|10984
03M03T00| 180655|13525|13576||13961|13994|14269|14457
03M03T00| 180659|7131|7157||7450|7467|7656|7802



Since the dates are more than the above, the below procedure should generated the number of days,
but unfortunately it gives an error and does not execute. Please let me know what is wrong with this package. Thanks.


CREATE OR REPLACE package pivot
as
type rc is ref cursor;
procedure data ( p_cursor in out rc );
end;
/



create or replace package body pivot as
procedure data(p_cursor in out rc )
is
l_stmt long;
begin

l_stmt := 'select routeid,premise ';
for x in ( select distinct rundate from testdata order by 1 )
loop
l_stmt := l_stmt ||
', max(decode(rundate,' || ''''|| x.rundate ||''''||
', ertread )) ' ||'"'|| x.rundate||'"';
end loop;
l_stmt := l_stmt || ' from testdata group by routeid,premise order by premise';

open p_cursor for l_stmt;
end;

end;
/

Any help is appreciated. Thanks

Tom Kyte
December 22, 2006 - 6:12 am UTC

no create table
   no insert into
      no look

i'm not going to sit here and create a sqlldr control file to load your data.

but it seems to work OK with more than 15

ops$tkyte%ORA10GR2> create or replace package body pivot as
  2  procedure data(p_cursor in out rc )
  3  is
  4      l_stmt long;
  5  begin
  6
  7      l_stmt := 'select routeid,premise ';
  8      for x in ( select distinct created rundate from all_users order by 1 )
  9      loop
 10          l_stmt := l_stmt ||
 11          ', max(decode(rundate,' || ''''|| x.rundate ||''''|| ', ertread )) ' ||'"'|| x.rundate||'"';
 12      end loop;
 13      l_stmt := l_stmt || ' from testdata group by routeid,premise order by premise';
 14          dbms_output.put_line( l_stmt );
 15
 16      open p_cursor for l_stmt;
 17  end;
 18
 19  end;
 20  /

Package body created.

ops$tkyte%ORA10GR2> variable x refcursor
ops$tkyte%ORA10GR2> exec pivot.data(:x)
select routeid,premise , max(decode(rundate,'30-JUN-05', ertread ))
"30-JUN-05", max(decode(rundate,'30-JUN-05', ertread )) "30-JUN-05",
max(decode(rundate,'30-JUN-05', ertread )) "30-JUN-05",
max(decode(rundate,'30-JUN-05', ertread )) "30-JUN-05",
max(decode(rundate,'30-JUN-05', ertread )) "30-JUN-05",
max(decode(rundate,'30-JUN-05', ertread )) "30-JUN-05",
max(decode(rundate,'30-JUN-05', ertread )) "30-JUN-05",
max(decode(rundate,'30-JUN-05', ertread )) "30-JUN-05",
max(decode(rundate,'30-JUN-05', ertread )) "30-JUN-05",
max(decode(rundate,'30-JUN-05', ertread )) "30-JUN-05",
max(decode(rundate,'30-JUN-05', ertread )) "30-JUN-05",
max(decode(rundate,'30-JUN-05', ertread )) "30-JUN-05",
max(decode(rundate,'30-JUN-05', ertread )) "30-JUN-05",
max(decode(rundate,'30-JUN-05', ertread )) "30-JUN-05",
max(decode(rundate,'30-JUN-05', ertread )) "30-JUN-05",
max(decode(rundate,'30-JUN-05', ertread )) "30-JUN-05",
max(decode(rundate,'31-JUL-06', ertread )) "31-JUL-06",
max(decode(rundate,'17-AUG-06', ertread )) "17-AUG-06",
max(decode(rundate,'27-AUG-06', ertread )) "27-AUG-06",
max(decode(rundate,'11-DEC-06', ertread )) "11-DEC-06",
max(decode(rundate,'12-DEC-06', ertread )) "12-DEC-06",
max(decode(rundate,'21-DEC-06', ertread )) "21-DEC-06" from testdata group by
routeid,premise order by premise
 

Transpose over 200 rows into columns

Nat, December 21, 2006 - 5:33 pm UTC

Here are the inserts for the above.

Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040614', '03M03T00', ' 180604', 5383);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040614', '03M03T00', ' 180609', 9640);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040614', '03M03T00', ' 180614', 11217);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040614', '03M03T00', ' 180624', 6309);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040614', '03M03T00', ' 180627', 7316);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040614', '03M03T00', ' 180630', 9035);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040614', '03M03T00', ' 180633', 10818);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040614', '03M03T00', ' 180637', 8743);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040614', '03M03T00', ' 180640', 7945);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040614', '03M03T00', ' 180645', 11183);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040614', '03M03T00', ' 180650', 10378);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040614', '03M03T00', ' 180655', 13525);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040614', '03M03T00', ' 180659', 7131);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040615', '03M03T00', ' 180604', 5403);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040615', '03M03T00', ' 180609', 9722);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040615', '03M03T00', ' 180614', 11256);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040615', '03M03T00', ' 180624', 6335);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040615', '03M03T00', ' 180627', 7327);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040615', '03M03T00', ' 180630', 9132);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040615', '03M03T00', ' 180633', 10827);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040615', '03M03T00', ' 180637', 8744);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040615', '03M03T00', ' 180640', 8001);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040615', '03M03T00', ' 180645', 11230);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040615', '03M03T00', ' 180650', 10379);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040615', '03M03T00', ' 180655', 13576);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040615', '03M03T00', ' 180659', 7157);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040617', '03M03T00', ' 180604', 5495);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040617', '03M03T00', ' 180609', 9761);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040617', '03M03T00', ' 180614', 11328);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040617', '03M03T00', ' 180624', 6373);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040617', '03M03T00', ' 180627', 7370);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040617', '03M03T00', ' 180630', 9597);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040617', '03M03T00', ' 180633', 11070);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040617', '03M03T00', ' 180637', 8869);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040617', '03M03T00', ' 180640', 8219);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040617', '03M03T00', ' 180645', 11588);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040617', '03M03T00', ' 180650', 10582);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040617', '03M03T00', ' 180655', 13961);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040617', '03M03T00', ' 180659', 7450);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040618', '03M03T00', ' 180659', 7467);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040618', '03M03T00', ' 180604', 5526);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040618', '03M03T00', ' 180609', 9905);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040618', '03M03T00', ' 180614', 11350);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040618', '03M03T00', ' 180624', 6486);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040618', '03M03T00', ' 180627', 7375);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040618', '03M03T00', ' 180630', 9643);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040618', '03M03T00', ' 180633', 11118);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040618', '03M03T00', ' 180637', 9018);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040618', '03M03T00', ' 180640', 8240);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040618', '03M03T00', ' 180645', 11635);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040618', '03M03T00', ' 180650', 10582);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040618', '03M03T00', ' 180655', 13994);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040619', '03M03T00', ' 180659', 7656);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040619', '03M03T00', ' 180604', 5531);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040619', '03M03T00', ' 180609', 9937);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040619', '03M03T00', ' 180614', 11393);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040619', '03M03T00', ' 180624', 6491);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040619', '03M03T00', ' 180627', 7513);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040619', '03M03T00', ' 180630', 9675);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040619', '03M03T00', ' 180633', 11175);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040619', '03M03T00', ' 180637', 9179);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040619', '03M03T00', ' 180640', 8280);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040619', '03M03T00', ' 180645', 11682);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040619', '03M03T00', ' 180650', 10682);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040619', '03M03T00', ' 180655', 14269);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040620', '03M03T00', ' 180604', 5610);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040620', '03M03T00', ' 180609', 10229);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040620', '03M03T00', ' 180614', 11744);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040620', '03M03T00', ' 180624', 6685);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040620', '03M03T00', ' 180627', 7697);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040620', '03M03T00', ' 180630', 9739);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040620', '03M03T00', ' 180633', 11271);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040620', '03M03T00', ' 180637', 9407);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040620', '03M03T00', ' 180640', 8521);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040620', '03M03T00', ' 180645', 12007);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040620', '03M03T00', ' 180650', 10984);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040620', '03M03T00', ' 180655', 14457);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040620', '03M03T00', ' 180659', 7802);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040621', '03M03T00', ' 180604', 5665);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040621', '03M03T00', ' 180609', 10307);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040621', '03M03T00', ' 180614', 11918);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040621', '03M03T00', ' 180624', 6804);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040621', '03M03T00', ' 180627', 7849);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040621', '03M03T00', ' 180630', 9782);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040621', '03M03T00', ' 180633', 11335);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040621', '03M03T00', ' 180637', 9413);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040621', '03M03T00', ' 180640', 8666);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040621', '03M03T00', ' 180645', 12059);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040621', '03M03T00', ' 180650', 10984);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040621', '03M03T00', ' 180655', 14524);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040621', '03M03T00', ' 180659', 7817);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040622', '03M03T00', ' 180604', 5768);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040622', '03M03T00', ' 180609', 10348);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040622', '03M03T00', ' 180614', 11984);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040622', '03M03T00', ' 180624', 6885);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040622', '03M03T00', ' 180627', 7850);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040622', '03M03T00', ' 180630', 9817);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040622', '03M03T00', ' 180633', 11389);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040622', '03M03T00', ' 180637', 9432);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040622', '03M03T00', ' 180640', 8719);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040622', '03M03T00', ' 180645', 12150);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040622', '03M03T00', ' 180650', 11002);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040622', '03M03T00', ' 180655', 14587);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040622', '03M03T00', ' 180659', 7839);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040623', '03M03T00', ' 180604', 5872);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040623', '03M03T00', ' 180609', 10375);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040623', '03M03T00', ' 180614', 12114);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040623', '03M03T00', ' 180624', 7036);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040623', '03M03T00', ' 180627', 7850);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040623', '03M03T00', ' 180630', 10028);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040623', '03M03T00', ' 180633', 11410);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040623', '03M03T00', ' 180637', 9472);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040623', '03M03T00', ' 180640', 8858);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040623', '03M03T00', ' 180645', 12310);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040623', '03M03T00', ' 180650', 11046);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040623', '03M03T00', ' 180655', 14853);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040623', '03M03T00', ' 180659', 7888);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040624', '03M03T00', ' 180604', 6043);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040624', '03M03T00', ' 180609', 10600);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040624', '03M03T00', ' 180614', 12471);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040624', '03M03T00', ' 180624', 7100);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040624', '03M03T00', ' 180627', 7850);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040624', '03M03T00', ' 180630', 10092);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040624', '03M03T00', ' 180633', 11415);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040624', '03M03T00', ' 180637', 9671);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040624', '03M03T00', ' 180640', 9100);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040624', '03M03T00', ' 180645', 12691);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040624', '03M03T00', ' 180650', 11090);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040624', '03M03T00', ' 180655', 14988);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040624', '03M03T00', ' 180659', 7961);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040625', '03M03T00', ' 180604', 6065);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040625', '03M03T00', ' 180609', 10685);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040625', '03M03T00', ' 180614', 12537);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040625', '03M03T00', ' 180624', 7303);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040625', '03M03T00', ' 180627', 7850);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040625', '03M03T00', ' 180630', 10164);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040625', '03M03T00', ' 180633', 11466);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040625', '03M03T00', ' 180637', 10138);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040625', '03M03T00', ' 180640', 9282);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040625', '03M03T00', ' 180645', 12908);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040625', '03M03T00', ' 180650', 11106);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040625', '03M03T00', ' 180655', 15061);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040625', '03M03T00', ' 180659', 8002);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040626', '03M03T00', ' 180604', 6093);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040626', '03M03T00', ' 180609', 10713);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040626', '03M03T00', ' 180614', 12664);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040626', '03M03T00', ' 180624', 7420);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040626', '03M03T00', ' 180627', 7850);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040626', '03M03T00', ' 180630', 10198);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040626', '03M03T00', ' 180633', 11495);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040626', '03M03T00', ' 180637', 10149);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040626', '03M03T00', ' 180640', 9299);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040626', '03M03T00', ' 180645', 12959);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040626', '03M03T00', ' 180650', 11123);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040626', '03M03T00', ' 180655', 15290);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040626', '03M03T00', ' 180659', 8098);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040627', '03M03T00', ' 180659', 8214);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040627', '03M03T00', ' 180604', 6262);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040627', '03M03T00', ' 180609', 10838);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040627', '03M03T00', ' 180614', 13000);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040627', '03M03T00', ' 180624', 7469);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040627', '03M03T00', ' 180627', 7850);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040627', '03M03T00', ' 180630', 10220);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040627', '03M03T00', ' 180633', 11531);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040627', '03M03T00', ' 180637', 10153);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040627', '03M03T00', ' 180640', 9477);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040627', '03M03T00', ' 180645', 13346);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040627', '03M03T00', ' 180650', 11215);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040627', '03M03T00', ' 180655', 15612);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040628', '03M03T00', ' 180604', 6370);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040628', '03M03T00', ' 180609', 10982);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040628', '03M03T00', ' 180614', 13206);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040628', '03M03T00', ' 180624', 7655);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040628', '03M03T00', ' 180627', 7850);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040628', '03M03T00', ' 180630', 10328);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040628', '03M03T00', ' 180633', 11605);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040628', '03M03T00', ' 180637', 10527);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040628', '03M03T00', ' 180640', 9672);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040628', '03M03T00', ' 180645', 13402);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040628', '03M03T00', ' 180650', 11231);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040628', '03M03T00', ' 180655', 15790);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040628', '03M03T00', ' 180659', 8269);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040629', '03M03T00', ' 180604', 6378);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040629', '03M03T00', ' 180609', 11013);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040629', '03M03T00', ' 180614', 13225);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040629', '03M03T00', ' 180624', 7671);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040629', '03M03T00', ' 180627', 7875);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040629', '03M03T00', ' 180630', 10365);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040629', '03M03T00', ' 180633', 11620);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040629', '03M03T00', ' 180637', 10667);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040629', '03M03T00', ' 180640', 9750);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040629', '03M03T00', ' 180645', 13451);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040629', '03M03T00', ' 180650', 11253);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040629', '03M03T00', ' 180655', 15852);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040629', '03M03T00', ' 180659', 8308);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040630', '03M03T00', ' 180604', 6417);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040630', '03M03T00', ' 180609', 11037);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040630', '03M03T00', ' 180614', 13240);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040630', '03M03T00', ' 180624', 7680);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040630', '03M03T00', ' 180627', 7939);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040630', '03M03T00', ' 180630', 10407);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040630', '03M03T00', ' 180633', 11629);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040630', '03M03T00', ' 180637', 10726);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040630', '03M03T00', ' 180640', 9829);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040630', '03M03T00', ' 180645', 13501);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040630', '03M03T00', ' 180650', 11313);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040630', '03M03T00', ' 180655', 16226);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040630', '03M03T00', ' 180659', 8399);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040701', '03M03T00', ' 180604', 6519);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040701', '03M03T00', ' 180609', 11109);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040701', '03M03T00', ' 180614', 13611);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040701', '03M03T00', ' 180624', 7899);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040701', '03M03T00', ' 180627', 7960);
Insert into TESTDATA (RUNDATE, ROUTEID, PREMISE, ERTREAD)
Values ('20040701', '0
Tom Kyte
December 22, 2006 - 6:14 am UTC

test cases are short
concise
yet 100% complete


I fail to see how the error could be "l_stmt undefined" - that would be a COMPILE time error (meaning you have no need for any data at all, procedure never compiled)

Transpose over 200 rows into columns

Nat, December 22, 2006 - 2:11 pm UTC

Tom,
Thanks for your help. I was able to execute the package as it took some time executing the procedure. The compilation was successful but when executing the package in debug mode, it gave me the error. But when executing without debug mode, I was able to get the desired results. Thanks once again for all your help.

column to row

Heiko, October 10, 2007 - 10:53 am UTC

Hi Tom,

I have a table with the following data:
create table t1(column_1 varchar2(30),
column_2 varchar2(30),
column_3 varchar2(30),
column_4 varchar2(30),
column_5 varchar2(30),
.
.
.
);
insert into t1 values ('a1','a2','a3','a4','a5',...);
insert into t1 values ('b1','b2','b3','b4','b5',...);
.
.
.

column_1 column_2 column_3 column_4 column_5 ...
---------------------------------------------------
a1 a2 a3 a4 a5 .
b1 b2 b3 b4 b5 .
. . . . . .
. . . . . .
. . . . . .

and I need output as:
row_1 row_2 ...
------------------------
a1 b1 .
a2 b2 .
a3 b3 .
a4 b4 .
a5 b5 .
. . .
. . .
. . .

any idea?

Thanks!
Heiko

Transform column data to row

Ann, April 01, 2008 - 6:02 pm UTC

Hi Tom,

Table T1
create table t1(a varchar2(30),
b varchar2(30),
c varchar2(30),
d varchar2(30));


insert into t1 values ('A1000', '27.68%','2.78%',',69.55%');
insert into t1 values ('B1000', '25.00%','5.00%','5.00%');

SELECT A, B, C, D
FROM T1;
A B C D
===== ===== ===== ======
A1000 27.68% 2.78% 69.55%
B1000 25.00% 5.00% 70.00%


help me in selecting from table T1 columns A B C D as rows

A PERCENT
====== ========
A1000 27.68%
A1000 2.78%
A1000 69.55%
B1000 25.00%
B1000 5.00%
B1000 70.00%

Thanks,
Tom Kyte
April 01, 2008 - 9:17 pm UTC

ops$tkyte%ORA11GR1> select a, col, data
  2    from t1
  3   unpivot ( DATA for COL in ( b,c,d ) )
  4  /

A                              C DATA
------------------------------ - ------------------------------
A1000                          B 27.68%
A1000                          C 2.78%
A1000                          D ,69.55%
B1000                          B 25.00%
B1000                          C 5.00%
B1000                          D 5.00%

6 rows selected.

Query to select columns as row

Ann, April 01, 2008 - 6:53 pm UTC

Hi Tom,

Table T1
create table t1(a varchar2(30),
b varchar2(30),
c varchar2(30),
d varchar2(30));


insert into t1 values ('A1000','27.68%','2.78%',',69.55%');
insert into t1 values ('B1000', '25.00%','5.00%','5.00%');
insert into t1 values ('C1000', 'N/A','75.00%','25.00%');
insert into t1 values ('D1000', 'N/A','N/A','N/A');
insert into t1 values ('E1000', '25.00%','N/A','75.00%');

SELECT A, B, C, D
FROM T1;
A B C D
===== ===== ===== ======
A1000 27.68% 2.78% 69.55%
B1000 25.00% 5.00% 70.00%
C1000 N/A 75.00% 25.00%
D1000 N/A N/A N/A
E1000 25.00% N/A 75.00%

help me with a query to select columns by rows from table T1 columns A B C D as rows.
If all B C and D columns is = N/A then select one row.
If any B C and D columns is not= N/A select show all columns in rows that is not=N/A

A PERCENT
====== ========
A1000 27.68%
A1000 2.78%
A1000 69.55%
B1000 25.00%
B1000 5.00%
B1000 70.00%
C1000 75.00%
C1000 25.00%
D1000 N/A
E1000 25.00%
E1000 75.00%

Thanks,

Tom Kyte
April 01, 2008 - 9:26 pm UTC

sigh, look in the other place you posted this exact same stuff.

and don't do that.

Transform column data to row

Ann, April 02, 2008 - 10:41 am UTC

Sorry about posting twice for the same stuff and not giving informaiton on what db this query to be run...

The query that you suggested run on Oracle 11g.
As UNIPIVOT is not supported in oracle 9i, can you suggest the equivalent select query to run on 9i.

Thanks,

Tom Kyte
April 02, 2008 - 11:57 am UTC

ops$tkyte%ORA9IR2> with data as (select level R from dual connect by level <= 3)
  2  select a, decode( r, 1, 'B', 2, 'C', 3, 'D' ) col, decode( r, 1, b, 2, c, 3, d ) val
  3    from t1, data
  4  /

A                              C VAL
------------------------------ - ------------------------------
A1000                          B 27.68%
B1000                          B 25.00%
C1000                          B N/A
D1000                          B N/A
E1000                          B 25.00%
A1000                          C 2.78%
B1000                          C 5.00%
C1000                          C 75.00%
D1000                          C N/A
E1000                          C N/A
A1000                          D ,69.55%
B1000                          D 5.00%
C1000                          D 25.00%
D1000                          D N/A
E1000                          D 75.00%

15 rows selected.

Transform values in the same colum into rows

Sandeep, April 14, 2008 - 6:19 pm UTC

Hi Tom,

I will use Ann's example:

Hi Tom,

Table T1
create table t1(a varchar2(30),
b varchar2(30));


insert into t1 values ('A1000','27.68%,2.78%,69.55%');

SELECT A, B FROM T1;
A B
===== =====
A1000 27.68%,2.78%,69.55%

Can you help me write a SQL which will give the same output Ann wanted? I am on Oracle 10g.

A PERCENT
====== ========
A1000 27.68%
A1000 2.78%
A1000 69.55%

Thanks,
Sandeep.

Tom Kyte
April 16, 2008 - 2:14 pm UTC

that was NOT Ann's example

Ann had a proper well formed schema, with columns

You have "a bad idea", the storing of a comma separated string as an attribute. That clearly should be THREE attributes.

please tell me you really have three columns in real life.

sunshine, April 29, 2008 - 5:09 am UTC

Hi Tom,
I have created a query which converts Columns to Rows.

mytable(CPR, UNIQUE_ID, HEAD01, AMOUNT01, HEAD02, AMOUNT02)

Select
0 as CPR,
UNIQUE_ID ,
HEAD01 as Name_Code ,
AMOUNT01 as Amount_Paid,

From mytable
Where HEAD01 is not NULL
and UNIQUE_ID in('FE20070820','FE20070821')
union all

Select
0 as CPR,
UNIQUE_ID ,
HEAD02 as Name_Code ,
AMOUNT02 as Amount_Paid,

From mytable
Where HEAD01 is not NULL
and UNIQUE_ID in('FE20070820','FE20070821')

Result is
CPR UNIQUE_ID NAME_CODE AMOUNT_PAID
0 FE20070820 'HEAD01' 300
0 FE20070820 'HEAD02' 300
0 FE20070821 'HEAD01' 100
0 FE20070821 'HEAD02' 300

Now i want the CPR to be 1 with MAX(Amount_Paid) when grouping by Unique_ID and if there amount are same then the on CPR to be 1
as

CPR UNIQUE_ID NAME_CODE AMOUNT_PAID
1 FE20070820 'HEAD01' 300
0 FE20070820 'HEAD02' 300
0 FE20070821 'HEAD01' 100
1 FE20070821 'HEAD02' 300

How to transform the Column to Row resultant dataset to show CPR 1 with MAX(Amount_Paid) other wise 0

by the way i have Head01 -- Head10 and Amout01-- Amount10 but for clearity and simplicity i have writen two only

Thanks in Advance
Tom Kyte
April 29, 2008 - 8:43 am UTC

no create
no inserts
no look

Storing Data Vertically

Butun, May 05, 2008 - 12:18 am UTC

Hi Tom,

If there are huge number of data elements for a file, say 8000, what is the better way of storing the data? Option 1 is keeping the columns as Rows (Vertical) or creating 8 tables of 1000 columns each?

The proper way should be to normalize the data and store it in proper normalized set of tables. But what to do if it is not possible to normalize the data?


Tom Kyte
May 05, 2008 - 2:51 am UTC

EAV models stink (entity - attribute - value). store data like that and it'll be the most secure data ever since

a) no one will be able to query it
b) even if they do, they will not have the patience to wait for the answer

It is always - repeat - it is ALWAYS possible to do the right thing. Statements like this:

... But what to do if it is not possible to ....


are always false. It is a choice you are purposely making - nothing more, nothing less.



Pivoting two tables

Prabhu, May 07, 2008 - 9:04 am UTC

Relating to this thread, I have this question. I have User_ID & Dept_id in Curr_dept. When table is refreshed a snapshot will be stored in prev_dept (something like Mar08 & Apr08). I need to now compare these two tables and produce a report like this. One user can belong to more than one dept! Dept could be deleted, added or changed for an user.
User Curr Prev Status
1 524 524
1 529 New
2 530 530
2 542 Deleted
3 413 818 Changed

CREATE TABLE curr_dept (user_id NUMBER, dept_id NUMBER);
CREATE TABLE prev_dept (user_id NUMBER, dept_id NUMBER);
INSERT INTO curr_dept VALUES (1, 524);
INSERT INTO curr_dept VALUES (1, 529);
INSERT INTO curr_dept VALUES (2, 530);
INSERT INTO curr_dept VALUES (3, 413);
INSERT INTO prev_dept VALUES (1, 524);
INSERT INTO prev_dept VALUES (2, 530);
INSERT INTO prev_dept VALUES (2, 542);
INSERT INTO prev_dept VALUES (3, 818);
COMMIT;

Please advise. Thanks.
Tom Kyte
May 08, 2008 - 2:38 am UTC

to pivot "two" tables is no different than one, or one hundred.

You have a "query" Q

select * from (Q);


Your way of recording data is, well, I'd have to say "not really good"


Your user=3, there is going to be no way really to get that one "changed". You have NO WAY to tie those two records together.

What if the data were:

INSERT INTO curr_dept VALUES (3, 413);

INSERT INTO prev_dept VALUES (3, 818);
INSERT INTO prev_dept VALUES (3, 111);

so, what now, is 111 change and 818 deleted, is 818 change and 111 deleted. Heck, why could it not be that 818 and 111 are deleted and 413 is new?????

This structure is not correct, you'll need to rethink your strategy from the ground up I'm afraid.

Prabhu, May 08, 2008 - 3:38 am UTC

Thanks Tom. Unfortunately, I am Not involved in table design and I can not do anything about it. Infact I was only trying to give a surprise report to the user; they didnt ask for :) But for my knowledge, how would you design in this scenario, if it is possible to report in this way at all?
Tom Kyte
May 12, 2008 - 8:20 am UTC

I think you missed my point.

You cannot get the report you want, your data does NOT support it.

Unless and until you can deterministically answer this question from me:

What if the data were:

INSERT INTO curr_dept VALUES (3, 413);

INSERT INTO prev_dept VALUES (3, 818);
INSERT INTO prev_dept VALUES (3, 111);

so, what now, is 111 change and 818 deleted, is 818 change and 111 deleted. Heck, why could it not be that 818 and 111 are deleted and 413 is new?????


you quite simply cannot generate your report.

These tables were not "designed".

columns into rows

vaibhav, September 01, 2008 - 6:12 am UTC

Hi,

I have something like this:

SELECT 'dummy1', '1', 'dummy2', '2', 'dummy3' , '3'
FROM dual;

How do i get the following output:

dummy1 1
dummy2 2
dummy3 3

Tom Kyte
September 01, 2008 - 1:13 pm UTC

ops$tkyte%ORA10GR2> with data as
  2  (select level l from dual connect by level <=3)
  3  select decode( l, 1, a1, 2, a2, 3, a3 ),
  4         decode( l, 1, b1, 2, b2, 3, b3 )
  5    from (SELECT 'dummy1' a1, '1' b1, 'dummy2' a2, '2' b2, 'dummy3' a3, '3' b3 FROM dual),
  6         data;

DECODE D
------ -
dummy1 1
dummy2 2
dummy3 3

A reader, September 01, 2008 - 6:22 pm UTC

Simply fantabulous

MVs with Analytics functions

Salman Syed, September 16, 2008 - 5:28 pm UTC

Tom,

Thanks a lot for your help in everything. Do you know if we can use Analytics functions (lag, lead etc.) with Materialized Views in 11g?

Thanks.
Tom Kyte
September 17, 2008 - 12:10 am UTC

define "use", you could use them before

Lag and Lead in MVs

Salman Syed, December 15, 2008 - 11:25 am UTC

What I mean is can we create Materialized Views that have lag and lead in teh create materialized view SQL.

E.g

Create materialized view v_lag_1 as

SELECT empno,
ename,
job,
sal,
LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev,
sal - LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_diff
FROM emp;

Tom Kyte
December 29, 2008 - 10:39 am UTC

ops$tkyte%ORA9IR2> create table emp as select * from scott.emp;

Table created.

ops$tkyte%ORA9IR2> alter table emp add constraint emp_pk primary key(empno);

Table altered.

ops$tkyte%ORA9IR2> create materialized view mv
  2  as
  3  SELECT empno,
  4         ename,
  5         job,
  6         sal,
  7         LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev,
  8         sal - LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_diff
  9  FROM   emp;

Materialized view created.

converting range into rows

A reader, November 18, 2010 - 6:10 am UTC

What SQL can give result using following data:
PRODUCT PRICE FROM_DT TO_DT
------------------------------ ---------- --------- ---------
XYZ 200 17-NOV-10 27-NOV-10

Result:
PRODUCT PRICE FROM_DT TO_DT
------------------------------ ---------- --------- --------- ---------
XYZ 200 17-NOV-10 18-NOV-10
XYZ 200 18-NOV-10 19-NOV-10
XYZ 200 19-NOV-10 20-NOV-10
XYZ 200 20-NOV-10 21-NOV-10
XYZ 200 21-NOV-10 22-NOV-10
XYZ 200 22-NOV-10 23-NOV-10
XYZ 200 23-NOV-10 24-NOV-10
XYZ 200 24-NOV-10 25-NOV-10
XYZ 200 25-NOV-10 26-NOV-10
XYZ 200 26-NOV-10 27-NOV-10
Tom Kyte
November 19, 2010 - 7:18 am UTC

ops$tkyte%ORA11GR2> create table t (prod varchar2(10), price number, sdt date, edt date );

Table created.

ops$tkyte%ORA11GR2> insert into t values( 'xyz',200,to_date('17-nov-2010'),to_date('22-nov-2010'));

1 row created.

ops$tkyte%ORA11GR2> insert into t values( 'abc',300,to_date('14-mar-2010'),to_date('17-mar-2010'));

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select *
  2    from t, table(cast(multiset(select sdt+rownum-1 from dual connect by level <= edt-sdt+1)as
  3    sys.odciDateList));

PROD            PRICE SDT       EDT       COLUMN_VA
---------- ---------- --------- --------- ---------
xyz               200 17-NOV-10 22-NOV-10 17-NOV-10
xyz               200 17-NOV-10 22-NOV-10 18-NOV-10
xyz               200 17-NOV-10 22-NOV-10 19-NOV-10
xyz               200 17-NOV-10 22-NOV-10 20-NOV-10
xyz               200 17-NOV-10 22-NOV-10 21-NOV-10
xyz               200 17-NOV-10 22-NOV-10 22-NOV-10
abc               300 14-MAR-10 17-MAR-10 14-MAR-10
abc               300 14-MAR-10 17-MAR-10 15-MAR-10
abc               300 14-MAR-10 17-MAR-10 16-MAR-10
abc               300 14-MAR-10 17-MAR-10 17-MAR-10

10 rows selected.

A reader, November 23, 2010 - 5:07 am UTC

Thanks a lot Tom for your response. As always, very compact & simple solution.

Advanced Pivoting

nawnadas, February 02, 2011 - 6:08 am UTC

Tom,

those are very usefull tips for pivoting and turing columns into rows, going through the above i got confused if lets say a businness has any requirments to be able to fetch data in the below fashion would it be possible.

Table A has below columns and data

Group A Group A.1 Group A.1.1 Group A.1.2
-----------------------------------------------
New Car Jaguar 01-Jan-2011 Sold
New Car Hyundai 02-Jan-2011 Not Sold
Old Car Jaguar 05-Jan-2011 Sold
Old Car Jaguar 10-Jan-2011 Not Sold
Old Car Hyundai 15-Jan-2011 Sold
Old Car Hyundai 17-Jan-2011 Sold
New Car Hyundai 18-Jan-2011 Sold

Can the above data from a table be fetched in the below fashion using a SQL query.

Group Date Status
-------------------------------
New Car
Jaguar
01-Jan-2011 Sold
Hyundai
02-Jan-2011 Not Sold
18-Jan-2011 Sold
Old Car
Jaguar
05-Jan-2011 Sold
10-Jan-2011 Not Sold

Hyundai
15-Jan-2011 Sold
17-Jan-2011 Sold

Thanks.
Tom Kyte
February 02, 2011 - 8:01 am UTC

no create table
no inserts

could it be done? at a glance I'd say probably, but we'd need more detail first.

Column to Rows???

Jerry, April 19, 2011 - 9:43 am UTC

I have reviewed the comments/solutions here and have not found one suitable to my challenge. The closet one was from Sandeep (Transform values in the same colum into rows).

I have three fields per row with the 3rd field containing multiple values (not my design, but I have to live with it) :

Field1 Field2 Field3
1 2 A100,A200,A300,A400,A500....

How do I split this out into multiple rows with only one Field3 value per row? I am using 10g.

Result should be:
Field1 Field2 Field3
1 2 A100
1 2 A200
1 2 A300
1 2 A400
1 2 A500

Any assistance you can give me is greatly appreciated!

Thanks,
Jerry

Columns to rows--unpivot without UNPIVOT: excluding target rows when transposed values are all NULL

Greg, July 18, 2011 - 10:45 pm UTC

Hi Tom,

I was reading the transposition question and example (a pre- 11g "unpivot") that Ann posted on April 1, 2008 and your response the following day. While I found both the problem and your solution to be very understandable, concise, and extremely helpful, there appears to be one small part of Ann's question that I think you did not address.

Ann had a two-part question, ...

If all B C and D columns is = N/A then select one row.
If any B C and D columns is not= N/A select show all columns in rows that is not=N/A


Your solution answered the second part, but not the first part. Your solution resulted in the transposition of all the original cells (i.e., three rows, each for original columns B, C, and D), including the case where A="D1000" and all B, C, and D values were "N/A." If you had appended an...
order by a, col
...clause to the end of the SQL statement, then it would have been clearer to see that all three original columns of "N/A" resulted in three rows instead of a single row that Ann was looking for. While I found this very helpful, insofar as learning how to transpose the full "grid" of data, if you will, I am interested to know how you would modify the SQL statement to answer the first part of Ann's question, which was to display a single row if B, C, and D were all "N/A."

Now, I realize you included the additional "COL" column in your result in order to clearly show how the decoded row values relate to the originally-named columns before the transpose, and why it is important to specify exactly how many rows should appear in the result (hence, the use of "connect by level..."). But suppose you omitted the "decode( r, 1, 'B', 2, 'C', 3, 'D' ) col" portion of the statement. Then would you simply add a WHERE clause to get the resultant single row of A="D1000", VAL="N/A"? Perhaps the following?...
where B<>'N/A' or C<>'N/A' or D<>'N/A'
Or is there a better way to handle this?

Finally, supposing Ann's example had NULL values instead of "N/A" text values. How would you modify the SQL statement so that no row would appear in the result if the transposed VAL column contained a NULL value? In other words, how to display only those rows that contain actual response values?

Thanks!
Greg
Tom Kyte
July 19, 2011 - 7:54 am UTC

ops$tkyte%ORA11GR2> with data as (select level r from dual connect by level <= 3)
  2  select a, decode( r, 1, 'B', 2, 'C', 3, 'D' ) col, decode( r, 1, b, 2, c, 3, d ) val
  3    from t1, data
  4   where decode( r, 1, b, 2, c, 3, d ) <> 'N/A'
  5      or (r=1 and b = 'N/A' and c = 'N/A' and d = 'N/A')
  6   order by a, col
  7  /

A                              C VAL
------------------------------ - ------------------------------
A1000                          B 27.68%
A1000                          C 2.78%
A1000                          D ,69.55%
B1000                          B 25.00%
B1000                          C 5.00%
B1000                          D 5.00%
C1000                          C 75.00%
C1000                          D 25.00%
D1000                          B N/A
E1000                          B 25.00%
E1000                          D 75.00%

11 rows selected.



it would be a where clause - yes. And if it were NULL instead of 'N/A', we would just replace

<> 'N/A' with "is not null"

and

= 'N/A' with "is null"


Columns to rows--unpivot without UNPIVOT: follow-up questions

Greg, July 19, 2011 - 10:00 pm UTC

Hi Tom,

Thank you very much for your helpful and prompt reply... I wasn't expecting that so soon. You really are fantastic! Anyway, having answered my previous "unpivot" question that related to Ann's original question, I'm hoping you might indulge me with another follow-up question that more closely resembles the actual "real-life" scenario I currently have.

Extending the example, let's say we have a second table, T2, containing the same key column, A, as in the first table, T1, plus additional response value columns, D, and E. Granted, this is already not a very good model, even with as few as just these two tables. But suppose we would like to combine these two tables, transpose the response value columns, B, C, D, E, and F, into rows, and exclude the resultant rows with NULL response values. In the code, below, in an inner subquery, I use a UNION ALL to combine the two tables and "unpivot" each, and then in an outer subquery I subset the resultant table to exclude the rows with NULL responses...

/* Table T1 */
create table t2 (
    a  varchar2(30),
    b  varchar2(30),
    c  varchar2(30),
    d  varchar2(30)
);

insert into t1 values( 'A1000', '27.68%',  '2.78%', '69.55%' );
insert into t1 values( 'B1000', '25.00%',  '5.00%',  '5.00%' );
insert into t1 values( 'C1000',     NULL, '75.00%', '25.00%' );
insert into t1 values( 'D1000',     NULL,     NULL,     NULL );
insert into t1 values( 'E1000', '25.00%',     NULL, '75.00%' );

select a, b, c, d
from t1;
/* T1: results in...
A      B       C       D
=====  ======  ======  ======
A1000  27.68%  2.78%   69.55%
B1000  25.00%  5.00%   70.00%
C1000          75.00%  25.00%
D1000                   
E1000  25.00%          75.00%
*/

/* Table T2 */
create table t2 (
    a  varchar2(30),
    e  varchar2(30),
    f  varchar2(30)
);

insert into t2 values( 'A1000',     NULL,     NULL );
insert into t2 values( 'B1000',     NULL, '15.50%' );
insert into t2 values( 'C1000', '52.25%', '65.00%' );
insert into t2 values( 'D1000', '66.67%',  '8.00%' );
insert into t2 values( 'E1000', '25.00%',     NULL );

select a, e, f
from t2;
/* T2: results in...
A      E       F     
=====  ======  ======
A1000                
B1000          15.50%
C1000  52.25%  65.00%
D1000  66.67%  8.00%   
E1000  25.00%   
*/

with data as (
    select
        level R
    from dual
    connect by
        level <= 6 
    )
select *
    from (  select 
                t1.a,
                decode( r, 1, 'B', 2, 'C', 3, 'D' ) col,
                decode( r, 1, b, 2, c, 3, d ) val
            from
                t1, data
            union all
            select 
                t2.a,
                decode( r, 4, 'E', 5, 'F') col,
                decode( r, 4, e, 5, f) val
            from
                t2, data
         )
    where 
        col is not null and
        val is not null
    order by a, col
;

/* Results in...
A      COL  VAL
=====  ===  ======
A1000  B    27.68%
A1000  C    2.78%
A1000  D    69.55%
B1000  B    25.00%
B1000  C    5.00%
B1000  D    5.00%
B1000  F    15.50%
C1000  C    75.00%
C1000  D    25.00%
C1000  E    52.25%
C1000  F    65.00%
D1000  E    66.67%
D1000  F    8.00% 
E1000  B    25.00%
E1000  D    75.00%
E1000  E    25.00%
*/
I deliberately structured table T2 to have 2 response value columns, E and F, instead of 3, as the original T1 table had, in order to see what happens if you specify "too many" expected rows (i.e., "connect by level <= 6"). A sixth response value column would be able to "fit" into the resultant sixth "placeholder" row. But without specifying a sixth column in the DECODE function, the result table still gets created when joined with the unspecified sixth column resulting in a NULL value for the sixth row. In this example, the value of COL in the sixth row is NULL. If the goal is to transpose the data and also exclude resultant rows with NULL reponse values, then it becomes convenient to simply subset where "COL is not null and VAL is not null." And this subsetting condition would hold even with additional specified rows, or, "levels."

This works just fine for me. But is subsetting in this fashion--via an outer subquery--a good way to achieve the desired results? Are subqueries even needed here? It certainly seems to make it convenient to subset and exclude the resultant rows with NULL responses, since they now fall under a single transposed column that can be referenced in a WHERE clause. But in a "real-life" scenario where there are many more rows and columns to deal with, is it an efficient method?

Say we have many columns to transpose across more than one table that have to be combined. Would a generic subsetting condition in an outer subquery such as in the example above suffice to cover any future number of additional columns that may be needed to transpose? Specifying the number of "levels" to some number greater than the total number of columns to be transposed across all the tables to be combined, the DECODE() values in each of the tables to be transposed and UNION'ed would have to be specified in the inner subquery, but the subsetting WHERE clause in the outer subquery would not have to be changed. Again, I agree the model is not a very good one, but if faced with such a scenario, does the approach I describe make sense? It returns the results I expect, but do you see any alternative methods, or improvements that I might make to this code, or problems that you think I should be aware of and possibly address?

Thank you very much, Tom, for your remarkable work, sharing your knowledge, your time, and your humor. As a programmer of nearly 20 years (primarily in SAS), I consider this website an education unto itself. Primarily for learning more about Oracle, but also, in no small way either, for seeing how a desire to teach and the positive feedback one can achieve from helping others, even just a few, can overcome what must often seem like never-ending frustration trying to communicate. Keep up the great work!
Tom Kyte
July 22, 2011 - 12:36 pm UTC

you would use a with subquery with as few rows as possible - perform the cartesian join and use decode to "pivot" if you don't have 11gR2's 'pivot' syntax.

And you'd "where" to keep the resulting rows you wanted to keep.

It is the way to accomplish the pivot, yes.

Pivot and Join with Two tables

Brian, July 24, 2011 - 7:40 pm UTC

Hi Tom,

I want to pivot a table and then join it with another table though the two tables do not have a column that relates them to each other.

I have an input file like this..
Products columns represent company products
Columns Q1 to Q12 represent specific quarters
Values in the columns equal sales (or whatever)

SALES TABLE (represents input file)

PRODUCT Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 Q12
A 1 2 3 4 5 6 7 8 9 10 11 12
B 1 2 3 4 5 6 7 8 9 10 11 12
C 1 2 3 4 5 6 7 8 9 10 11 12
D 1 2 3 4 5 6 7 8 9 10 11 12
E 1 2 3 4 5 6 7 8 9 10 11 12

Period look up table:

Q1 = 100
Q2 = 200
Q3 = 300
and so on...

PERIOD TABLE

PERIOD_PK PERIOD_NAME
100 2010 Q1
200 2010 Q2
300 2010 Q3
400 2010 Q4
500 2011 Q1
600 2011 Q2
700 2011 Q3
800 2011 Q4
900 2012 Q1
1000 2012 Q2
1100 2012 Q3
1200 2012 Q4

I need to take the columns from the Sales table and transform each cell into a row while joining it with the Product table so it looks like this...

PRODUCT PERIOD_NAME PERIOD_PK VALUE_NUM
A 2010 Q1 100 1
A 2010 Q2 200 2
A 2010 Q3 300 3
A 2010 Q4 400 4
A 2011 Q1 500 5
A 2011 Q2 600 6
A 2011 Q3 700 7
A 2011 Q4 800 8
A 2012 Q1 900 9
A 2012 Q2 1000 10
A 2012 Q3 1100 11
A 2012 Q4 1200 12
B 2010 Q1 100 1
B 2010 Q2 200 2
B 2010 Q3 300 3
B 2010 Q4 400 4
B 2011 Q1 500 5
B 2011 Q2 600 6
B 2011 Q3 700 7
B 2011 Q4 800 8
B 2012 Q1 900 9
B 2012 Q2 1000 10
B 2012 Q3 1100 11
B 2012 Q4 1200 12

and so on for Products C, D, and E.

Any help would be greatly appreciated!

Thanks Tom,
Brian
Tom Kyte
July 27, 2011 - 7:58 pm UTC

I want to pivot a table and then join it with another table though the two tables do not have a column that relates them to each other.


well, that is a rather *strange* request you know. "I want to join two tables that have no join conditions..."

but in short

no create
no inserts
never going to look

if you want a sql statement as output - you need to provide create tables, inserts into them, an explanation of the output and some sample output to clarify with that.

dividing column value into rows

Praveen Ray, August 11, 2011 - 11:21 am UTC

Hi Tom,

Suppose a table having row like:
col1=a, col2=100^200^300 col3=10^20^30

Now, I am asked to write a query so that the result set should be:

a 100 10
a 200 10
a 300 10
a 100 20
a 200 20
...

where col2 and col3 can have N numeric values separated by (say ^)

Regards,
Praveen Ray
Tom Kyte
August 14, 2011 - 7:59 pm UTC

boy did some one mess up their data model or *what*

assuming you get a SINGLE row so we can cartesian join - this'll do it


ops$tkyte%ORA11GR2> create table t ( col1 varchar2(1) primary key, col2 varchar2(100), col3 varchar2(100) );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t values ( 'a', '100^200^300', '10^20^30' );

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> variable bind varchar2(1)
ops$tkyte%ORA11GR2> exec :bind := 'a';

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> with data
  2  as
  3  (
  4  select
  5    col1,
  6    trim( substr (col2_new,
  7          instr (col2_new, '^', 1, level  ) + 1,
  8          instr (col2_new, '^', 1, level+1)
  9             - instr (col2_new, '^', 1, level) -1 ) )
 10      as col2,
 11    trim( substr (col3_new,
 12          instr (col3_new, '^', 1, level  ) + 1,
 13          instr (col3_new, '^', 1, level+1)
 14             - instr (col3_new, '^', 1, level) -1 ) )
 15      as col3
 16     from (select col1,
 17                  '^'||col2||'^' col2_new,
 18                  '^'||col3||'^' col3_new,
 19                                  length(col2)-length(replace(col2,'^',''))+1 max_level
 20             from t
 21            where col1 = :bind )
 22   connect by level <= max_level
 23   )
 24  select d1.col1, d1.col2, d2.col3
 25    from data d1, data d2;

C COL2       COL3
- ---------- ----------
a 100        10
a 100        20
a 100        30
a 200        10
a 200        20
a 200        30
a 300        10
a 300        20
a 300        30

9 rows selected.

Query result in line

Cristian Fernandes, August 29, 2011 - 7:50 am UTC

Hello guys, I have a query that show me:

SELECT EMPCOD, EMPCONNOME, EMPCONFONE, EMPCONEMAI
FROM EMP0103
WHERE EMPCOD= 434
EMPCOD EMPCONNOME EMPCONFONE EMPCONEMAI 
434      Ronaldo       11 25411414    compras@gra.br 
434    Ronaldo      11 21454117 compras2@gra.br 
434    Sandro       13 25418745 alcarin@br.com.br 


I need that this query return in only one line as below:
EMPCOD1 EMPCONNOME1 EMPCONFONE1 EMPCONEMAI1     EMPCOD2 EMPCONNOME2 EMPCONFONE2 EMPCONEMAI2
434 Ronaldo 11 25411414 compras@gra.br 434 Ronaldo 11 21454117   compras2@gra.br


I´m exporting a table data and I need to export in a line format. If someone could help me to solve this issue, I will feel realy glad.

Thanks
Tom Kyte
August 30, 2011 - 5:07 pm UTC

no create
no inserts
no looks
and no promises, I don't know if this can be done, I didn't really read it, but if you want a query from me, I need a test case from you I can run first.

Reference

Michel Cadot, August 31, 2011 - 3:05 am UTC


For information, same question posted and discussed there:
http://www.orafaq.com/forum/mv/msg/174344/521387/102589/#msg_521387

Regards
Michel

Not working for mulitple records

Ganesh, November 30, 2011 - 10:58 am UTC

As Praveen Ray described above I have similar situation here and obviously having multiple records.
col1=a, col2=100^200^300 col3=10^20^30
col1=b, col2=110^210^310^410 col3=11^21^31^41
col1=c, col2=120^220^320 col3=12^22^32


Your solution does not seem to work if there are more than one record. Am I missing something?
Tom Kyte
December 01, 2011 - 7:27 am UTC

ops$tkyte%ORA11GR2> create table t ( col1 varchar2(1) primary key, col2 varchar2(100), col3 varchar2(100) );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t values ( 'a', '100^200^300', '10^20^30' );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 'b', '110^210^310^410', '11^21^31^41' );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 'c', '120^220^320', '12^22^32' );

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select t.col1, substr(x.column_value,1,instr(x.column_value,',')-1) c2,
  2                 substr(x.column_value,instr(x.column_value,',')+1) c3
  3    from (select col1, '^'||col2||'^' col2_new, '^'||col3||'^' col3_new from t) t,
  4         table( cast( multiset(
  5  select
  6    trim( substr (col2_new,
  7          instr (col2_new, '^', 1, level  ) + 1,
  8          instr (col2_new, '^', 1, level+1)
  9             - instr (col2_new, '^', 1, level) -1 ) ) || ',' ||
 10    trim( substr (col3_new,
 11          instr (col3_new, '^', 1, level) + 1,
 12          instr (col3_new, '^', 1, level+1)
 13             - instr (col3_new, '^', 1, level) -1 ) )
 14    from dual
 15  connect by level <= length(col2_new)-length(replace(col2_new,'^',''))-1
 16         ) as sys.odciVarchar2List ) ) x
 17  /

C C2         C3
- ---------- ----------
a 100        10
a 200        20
a 300        30
b 110        11
b 210        21
b 310        31
b 410        41
c 120        12
c 220        22
c 320        32

10 rows selected.

Thanks

Ganesh, December 02, 2011 - 9:13 am UTC

Many thanks Tom.
Even after 15 years working with Oracle it is amazing to know how much we can do with SQLs.

Ordering Columns in a Table DB 10g, 11g

Abubaker, October 05, 2019 - 12:55 pm UTC

Hi Tom,

Is there any solution to re-order the columns sequence in table using Oracle DB 10.2g and 11g.

Thanks
Abubaker

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.