Another Way to Solve Last Problem.

http://www.oracle.com/technology/oramag/code/tips2004/050304.html


1. create a function to concatenate strings.

CREATE OR REPLACE FUNCTION rowtocol( 
       p_slct IN VARCHAR2,
       p_dlmtr IN VARCHAR2 DEFAULT ',' 
) RETURN VARCHAR2 

  AUTHID CURRENT_USER AS

TYPE c_refcur IS REF CURSOR;

     lc_str VARCHAR2(4000);
     lc_colval VARCHAR2(4000);
     c_dummy c_refcur;

     BEGIN

     OPEN c_dummy FOR p_slct;
     LOOP
       FETCH c_dummy INTO lc_colval;
       EXIT WHEN c_dummy%NOTFOUND;
       lc_str := lc_str || p_dlmtr || lc_colval;
     END LOOP;

     CLOSE c_dummy;
     RETURN SUBSTR(lc_str,2);

     /* 
     EXCEPTION 
     WHEN OTHERS THEN
       lc_str := SQLERRM;
  
       IF c_dummy%ISOPEN THEN
          CLOSE c_dummy;
       END IF;
  
       RETURN lc_str;
     */
    END;


 Usage :

select distinct t.author, 
          rowtocol('select book from table_name where author=''' || t.author|| '''')
from table_name t
;


 or


select t.author, rowtocol('select book from table_name where author=''' || t.author|| '''', '#')
from table_name t
group by t.author
;

 


cons: less natural than user-defined aggregate function 

pros: more flexible, aggregate function could take only one parameter

 

阅读更多

Software Engineer: Another way to go to USA(job)

09-15

U.S. based software company is looking for experienced software rnprogrammers who will be working in Beijing branch for business rnapplication (Computerized Maintenance Management System) development. rnThe company provides you with the following benefits:rn1.The most advanced Microsoft .NET technology development environment: rn.NET Smart Client (Windows Form) implementation, ASP.NET on handheld devicesrnBusiness layer: IIS (Web Form) + Web Services (ASP.NET+ADO.NET)rnDatabase: MS SQL Serverrn2.English is used in programming and documentation rnIt’s a great chance to improve your English; we’ll help you!rn3.An opportunity to work in USArnQualified team members may be transferred to our headquarter, located in rnSilicon Valley.rnrnSenior Software Engineer (Team leader, full-time employee)rnThe following requirements must be satisfied:rn1. 2+ years business database application experience with one year on .NETrn2. Computer Science or equivalent bachelor degree or above rn3. .NET, C#, MS SQL Server, IIS, Web Service, HTML, XML, UML,Javascript rn4. Good command of English rnDesired:rn1. CET-6, TOEFL, GRE or other English certificates rn2. MCAD, MCSD, MCSE, MCDBA or other computer certificates rnrnJunior Software Engineer (full-time employee)rnThe following requirements must be satisfied:rn1. .NET database application experience using C# rn2. Computer Science or equivalent bachelor degree or abovern3. .NET + C# + MS SQL Server or .NET (ASP.NET) + Javascript + IIS+ rn SQL Serverrn4. Good command of English rnDesired:rn1. CET-6, TOEFL, GRE or other English certificates rn2. MCAD, MCSD, MCSE, MCDBA or other computer licensesrnrnInternship or Part-timernPotentially, you will be our full-time employee in the future; will pay rndepending on your experience and contribution to the company.rnThe following requirements are preferred:rn1. Microsoft .NET database application experience using C# or VB.NETrn2. Computer Science or equivalent students of famous universitiesrn3. .NET + C# + MS SQL Server or .NET + C# + IIS+ MS SQL Serverrn4. Good command of EnglishrnrnPlease send your resume in English to lxwjackie72@hotmail.com with job rntitle as the 'Subject'.rn

没有更多推荐了,返回首页