Home » SQL & PL/SQL » SQL & PL/SQL » creating an array out of several rows of data. (Oracle 8i)
creating an array out of several rows of data. [message #323321] Wed, 28 May 2008 03:36 Go to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

Hi all,

I have some SQL that returns a list of project ids and the task ids associated with the projects.
This obviously causes the project ids to be duplicated by the number of task ids on the project
e.g.

PROJECT     TASK
D01234      SYST
D01234      ADM
D01234      CUT
D01234      REGT
D01234      SYST
D04321      ADM
D04321      DEV
D04321      DES


What I would like is to be able to display a single line for each project, with a comma separated list of TASKs.

e.g.
PROJECT   TASKS
D01234    SYST, ADM, CUT, REGT, SYST
D04321    ADM, DEV, DES


can anyone tell me how I can do this?

My SQL currently is
SELECT
PROJECT_CODE,
PREXTERNALID
FROM
niku.srm_projects p,
niku.prtask t

WHERE
p.id = t.prprojectid


thanks in advance,

Matt
Re: creating an array out of several rows of data. [message #323338 is a reply to message #323321] Wed, 28 May 2008 04:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is the classical "pivot" query we answer several times per week.
So just search.

Regards
Michel
Re: creating an array out of several rows of data. [message #323352 is a reply to message #323338] Wed, 28 May 2008 04:54 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

A Good Example shows in Oracle site
http://www.oracle.com/technology/oramag/code/tips2004/050304.html

Cheers
Soumen
Re: creating an array out of several rows of data. [message #323353 is a reply to message #323352] Wed, 28 May 2008 04:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are many better ones here.

Regards
Michel
Re: creating an array out of several rows of data. [message #323356 is a reply to message #323338] Wed, 28 May 2008 05:08 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

apologies, I did not know the terminology of this, so didnt really have a clue what to search for..

searching now, although lots of the results are of you closing peoples threads and telling them to search for pivot queries Laughing

[Updated on: Wed, 28 May 2008 05:11]

Report message to a moderator

Re: creating an array out of several rows of data. [message #323470 is a reply to message #323356] Wed, 28 May 2008 09:30 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
This looks like more of a string aggregation problem to me. Since you are using 8i, your options are limited. The following example should help:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:229614022562
Re: creating an array out of several rows of data. [message #323502 is a reply to message #323321] Wed, 28 May 2008 10:49 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
For pivots, try these:

Kevin Meade's OraFAQ Blog

Example of Data Pivots in SQL (rows to columns and columns to rows)

I like using SYS_CONNECT_BY_PATH.

OracleŽ Database SQL Reference SYS_CONNECT_BY_PATH

Here is how it works:

SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------
 EMP_ID                                    NOT NULL NUMBER
 ENAME                                     NOT NULL VARCHAR2(30)
 MGR_EMP_ID                                         NUMBER

SQL> select count(*) from emp;

  COUNT(*)
----------
         3

SQL> select * from emp;

    EMP_ID ENAME                          MGR_EMP_ID
---------- ------------------------------ ----------
         1 emp1
         2 emp2                                    1
         3 emp3                                    2

SQL> 
SQL> select ename,rownum rowno
  2  from emp
  3  order by ename
  4  /

ENAME                               ROWNO
------------------------------ ----------
emp1                                    1
emp2                                    2
emp3                                    3

SQL> 
SQL> col ename_string format a60
SQL> 
SQL> select sys_connect_by_path(ename,',') ename_string,connect_by_isleaf is_leaf
  2  from (
  3         select ename,rownum rowno
  4         from emp
  5         order by ename
  6       )
  7  connect by prior rowno = rowno - 1
  8  /

ENAME_STRING                                                    IS_LEAF
------------------------------------------------------------ ----------
,emp1                                                                 0
,emp1,emp2                                                            0
,emp1,emp2,emp3                                                       1
,emp2                                                                 0
,emp2,emp3                                                            1
,emp3                                                                 1

6 rows selected.

SQL> 
SQL> select sys_connect_by_path(ename,',') ename_string,connect_by_isleaf is_leaf
  2  from (
  3         select ename,rownum rowno
  4         from emp
  5         order by ename
  6       )
  7  where connect_by_isleaf = 1
  8  connect by prior rowno = rowno - 1
  9  /

ENAME_STRING                                                    IS_LEAF
------------------------------------------------------------ ----------
,emp1,emp2,emp3                                                       1
,emp2,emp3                                                            1
,emp3                                                                 1

SQL> 
SQL> select sys_connect_by_path(ename,',') ename_string,connect_by_isleaf is_leaf
  2  from (
  3         select ename,rownum rowno
  4         from emp
  5         order by ename
  6       )
  7  connect by prior rowno = rowno - 1
  8  start with rowno = 1
  9  /

ENAME_STRING                                                    IS_LEAF
------------------------------------------------------------ ----------
,emp1                                                                 0
,emp1,emp2                                                            0
,emp1,emp2,emp3                                                       1

SQL> 
SQL> select sys_connect_by_path(ename,',') ename_string,connect_by_isleaf is_leaf
  2  from (
  3         select ename,rownum rowno
  4         from emp
  5         order by ename
  6       )
  7  where connect_by_isleaf = 1
  8  connect by prior rowno = rowno - 1
  9  start with rowno = 1
 10  /

ENAME_STRING                                                    IS_LEAF
------------------------------------------------------------ ----------
,emp1,emp2,emp3                                                       1

SQL> 
SQL> select substr(sys_connect_by_path(ename,','),2) ename_string,connect_by_isleaf is_leaf
  2  from (
  3         select ename,rownum rowno
  4         from emp
  5         order by ename
  6       )
  7  where connect_by_isleaf = 1
  8  connect by prior rowno = rowno - 1
  9  start with rowno = 1
 10  /

ENAME_STRING                                                    IS_LEAF
------------------------------------------------------------ ----------
emp1,emp2,emp3                                                        1


This is not always the best way to create a comma delimited string. Indeed, it requires the faking of a hierarchy and anytime we see code that tries to "force" its way to a solution, we have to consider it twice for things like intuition and performance etc. But this method is a pretty standard and very convenient implementation of string composition for short strings.

There is also the idea of using a generice plsql function that accepts a refcursor and returns a delimited string. I will let you write that one.

Lastly remember, that there is a limite to this of the maximmum size of a varchar2 string which is 4000 bytes, and that these functions a specific to 10g (SYS_CONNECT_BY_PATH, CONNECT_BY_ISLEAF).

Good luck, Kevin

desc emp

select ename,rownum rowno
from emp
order by ename
/

col ename_string format a60

select sys_connect_by_path(ename,',') ename_string,connect_by_isleaf is_leaf
from (
       select ename,rownum rowno
       from emp
       order by ename
     )
connect by prior rowno = rowno - 1
/

select sys_connect_by_path(ename,',') ename_string,connect_by_isleaf is_leaf
from (
       select ename,rownum rowno
       from emp
       order by ename
     )
where connect_by_isleaf = 1
connect by prior rowno = rowno - 1
/

select sys_connect_by_path(ename,',') ename_string,connect_by_isleaf is_leaf
from (
       select ename,rownum rowno
       from emp
       order by ename
     )
connect by prior rowno = rowno - 1
start with rowno = 1
/

select sys_connect_by_path(ename,',') ename_string,connect_by_isleaf is_leaf
from (
       select ename,rownum rowno
       from emp
       order by ename
     )
where connect_by_isleaf = 1
connect by prior rowno = rowno - 1
start with rowno = 1
/

select substr(sys_connect_by_path(ename,','),2) ename_string,connect_by_isleaf is_leaf
from (
       select ename,rownum rowno
       from emp
       order by ename
     )
where connect_by_isleaf = 1
connect by prior rowno = rowno - 1
start with rowno = 1
/
Re: creating an array out of several rows of data. [message #323514 is a reply to message #323502] Wed, 28 May 2008 11:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But all this is unavailable in 8i, OP's version.

Regards
Michel
Re: creating an array out of several rows of data. [message #323531 is a reply to message #323321] Wed, 28 May 2008 12:24 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
the pivot information in my posts works on any supported version of Oracle database.

dbms_sql.describe_columns is also available for 8i should the OP desire to write their own plsql function.

Even though the other material is 10g necessary, it is still good to see what awaits you.

Thanks, Kevin
Re: creating an array out of several rows of data. [message #323540 is a reply to message #323531] Wed, 28 May 2008 13:12 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
If we are talking about things to come, then my favorite is the undocumented wm_concat function. Hopefully, someday they will document it, so it will be considered safe, and maybe even add the ability to order the concatenated values.

  1  select d.dname, wm_concat (e.ename) as enames
  2  from   dept d, emp e
  3  where  d.deptno = e.deptno
  4* group  by d.dname
SCOTT@orcl_11g> /

DNAME          ENAMES
-------------- ---------------------------------------------
ACCOUNTING     CLARK,KING,MILLER
RESEARCH       SMITH,FORD,ADAMS,SCOTT,JONES
SALES          ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD


Previous Topic: How do I obtain cursor field names from cursor into variable
Next Topic: ora-01847 error, can't figure out why
Goto Forum:
  


Current Time: Wed Dec 07 04:53:02 CST 2016

Total time taken to generate the page: 0.08992 seconds