Home » SQL & PL/SQL » SQL & PL/SQL » How to do this using procedure
How to do this using procedure [message #224085] Mon, 12 March 2007 23:57 Go to next message
zinclinc
Messages: 2
Registered: March 2007
Location: hyd
Junior Member
Hi

I am stuck and i need help with this prob


i have a table with one column and 26 rows
e.g table name = letters
column name = alphabets
and column contents as 'a','b',......'z'

now how do insert these values in a new table letters_new
such that it will have one row and 26 columns
where columns contents will be these a,b,c...z alphabets ???


thanx in advance
Re: How to do this using procedure [message #224094 is a reply to message #224085] Tue, 13 March 2007 01:26 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
search on the forum for pivoting
(I have never used such functionality, but all questions in that area are answered in the direction of pivoting)
Re: How to do this using procedure [message #224609 is a reply to message #224094] Wed, 14 March 2007 14:10 Go to previous messageGo to next message
Ivan
Messages: 180
Registered: June 2000
Senior Member
May not be a good approach, but it works Smile

SQL> create table letters (letter varchar2 (1));

Table created

SQL> begin
  2     for i in 97 .. 122 loop
  3        insert into letters values (chr (i));
  4     end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed

SQL> declare
  2     lt dbms_sql.varchar2_table;
  3     lv varchar2 (10000) := 'create table letter_record as select ';
  4  begin
  5     select letter
  6       bulk collect
  7       into lt
  8       from letters
  9      order by letter;
 10     --
 11     for i in lt.first .. lt.last
 12     loop
 13        if i > 1
 14        then
 15           lv := lv || ', ''' || lt(i) || ''' ' || lt(i);
 16        else
 17           lv := lv || '''' || lt(i) || ''' ' || lt(i);
 18        end if;
 19     end loop;
 20     --
 21     lv := lv || '  from dual';
 22     --
 23     execute immediate lv;
 24  end;
 25  /

PL/SQL procedure successfully completed

SQL> desc letter_record
Name Type    Nullable Default Comments 
---- ------- -------- ------- -------- 
A    CHAR(1) Y                         
B    CHAR(1) Y                         
C    CHAR(1) Y                         
D    CHAR(1) Y                         
E    CHAR(1) Y                         
F    CHAR(1) Y                         
G    CHAR(1) Y                         
H    CHAR(1) Y                         
I    CHAR(1) Y                         
J    CHAR(1) Y                         
K    CHAR(1) Y                         
L    CHAR(1) Y                         
M    CHAR(1) Y                         
N    CHAR(1) Y                         
O    CHAR(1) Y                         
P    CHAR(1) Y                         
Q    CHAR(1) Y                         
R    CHAR(1) Y                         
S    CHAR(1) Y                         
T    CHAR(1) Y                         
U    CHAR(1) Y                         
V    CHAR(1) Y                         
W    CHAR(1) Y                         
X    CHAR(1) Y                         
Y    CHAR(1) Y                         
Z    CHAR(1) Y                         

Re: How to do this using procedure [message #224830 is a reply to message #224609] Thu, 15 March 2007 11:20 Go to previous messageGo to next message
zinclinc
Messages: 2
Registered: March 2007
Location: hyd
Junior Member
Thnx Ivan

seems bit different

will let you know once i get to know the other way of doin this


cheers,
zinclinc
Re: How to do this using procedure [message #225298 is a reply to message #224085] Mon, 19 March 2007 11:05 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member


create table foo (alph varchar2(1)) ;

insert into foo
select chr(level+64) from dual
connect by level < 27 ;
Commit;



select ltrim(max(sys_connect_by_path(alph,',') ),',')
from (
select alph ,
row_number() over (order by alph) rn
from foo
)
connect by rn=prior rn+1
start with rn=1
SQL> /

LTRIM(MAX(SYS_CONNECT_BY_PATH(ALPH,',')),',')
-----------------------------------------------------------------------------
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z



Srini
icon14.gif  Re: How to do this using procedure [message #225319 is a reply to message #225298] Mon, 19 March 2007 12:32 Go to previous message
Ivan
Messages: 180
Registered: June 2000
Senior Member
Applause, Srini!
That's awesome!
Previous Topic: Case Statement
Next Topic: Substitute for the IIF function used in Access
Goto Forum:
  


Current Time: Wed Dec 07 14:33:23 CST 2016

Total time taken to generate the page: 0.08931 seconds