Columns to Rows

Setup

Run the code on this page in SQL*Plus to create the sample tables, data, etc. used by the examples in this section.

Be sure to read Using SQL Snippets ™ before executing any of these setup steps.

set linesize 60

column source format a6
column name   format a4

create table t
(
  key varchar2(3) ,
  c1  varchar2(5) ,
  c2  varchar2(5) ,
  c3  varchar2(5) ,
  n1  number
);

insert into t values ( 'r1' , 'v1' , 'v2' , 'v3', 1  );
insert into t values ( 'r2' , 'v1' , 'v2' , null, 2  );
insert into t values ( 'r3' , 'v1' , null , 'v3', 3  );
insert into t values ( 'r4' , 'v1' , null , null, 4  );
insert into t values ( 'r5' , null , 'v2' , 'v3', 5  );
insert into t values ( 'r6' , null , 'v2' , null, 6  );
insert into t values ( 'r7' , null , null , 'v3', 7  );
insert into t values ( 'r8' , null , null , null, 8  );

commit;

create type name_value_pair as object( name varchar2(30), value varchar2(5) );
/

create type name_value_varray as varray(10) of name_value_pair ;
/

create type varray_type as varray(10) of varchar2(5);
/

 



Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-12028.html]SQL Snippets: Columns to Rows - Setup[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-12028.html">SQL Snippets: Columns to Rows - Setup</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Columns to Rows - Setup
  • URL (href): http://www.sqlsnippets.com/en/topic-12028.html