Home » Developer & Programmer » Reports & Discoverer » multiples rows into one row. (Oracle 10XE , form 6i)
multiples rows into one row. [message #675368] Sun, 24 March 2019 10:48 Go to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
create table student (stuid number(6) primary key,name varchar2(30),mob varchar2(20));

insert into student (1,'abc','92333444444');
insert into student (2,'aac','92333004444');
insert into student (3,'avc','92333004444');
insert into student (4,'afc','92333233444');
insert into student (5,'atc','92333233444');
insert into student (6,'arc','92333444444');
insert into student (7,'aec','92333444444');
insert into student (8,'atc','92333123444');
insert into student (9,'arc','92332334444');
insert into student (10,'aec','92333444444');

i want to get the result as:

mob             Stuid
---------------------------------------------------
92333444444     (1,6,7,10)
92333004444     (2,3)
92332334444     (4,5,9)


Quote:

SQL> CREATE TYPE number_7_2_ntt AS TABLE OF NUMBER(7,2);
2 /

Type created.

SQL> SELECT mob
2 , CAST(COLLECT(stuid) AS number_7_2_ntt) AS sals
3 FROM student
4 GROUP BY
5 mob;

mob SALS
---------- -------------------------------------------------------------------------
92333444444 Number_ntt(1,6,7,10)
92333004444 Number_ntt(2,3)
92332334444 Number_ntt(4,5,9)

how i can use this query in reports.it return error:
00-600: internal error code : argument [17069]/

please advised.
Re: multiples rows into one row. [message #675369 is a reply to message #675368] Sun, 24 March 2019 13:39 Go to previous messageGo to next message
John Watson
Messages: 8076
Registered: January 2010
Location: Global Village
Senior Member
I am not any sort of Forms bloke, but I have heard that if Forms doesn't understand something the answer may be to create a view that does the work and in Forms query the view.
Re: multiples rows into one row. [message #675370 is a reply to message #675368] Sun, 24 March 2019 14:06 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Apart that the test case is not correct:
SQL> insert into student (1,'abc','92333444444');
insert into student (1,'abc','92333444444')
                     *
ERROR at line 1:
ORA-00928: missing SELECT keyword
And that the result from it is not correct
And that in your more than 200 topics you fed back and thanked people who spent time to help you only (about) once, here's how you can do it:
SQL> select mob,
  6  /
MOB                  STUID
-------------------- ------------------------------
92332334444          (9)
92333004444          (2,3)
92333123444          (8)
92333233444          (4,5)
92333444444          (1,6,7,10)
Re: multiples rows into one row. [message #675371 is a reply to message #675370] Sun, 24 March 2019 21:05 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
create table student (stuid number(6) primary key,name varchar2(30),mob varchar2(20));

insert into student values (1,'abc','92333444444');
insert into student values (2,'aac','92333004444');
insert into student values (3,'avc','92333004444');
insert into student values (4,'afc','92333233444');
insert into student values (5,'atc','92333233444');
insert into student values (6,'arc','92333444444');
insert into student values (7,'aec','92333444444');
insert into student values (8,'atc','92333123444');
insert into student values (9,'arc','92332334444');
insert into student values (10,'aec','92333444444');

i want to get the result as:

mob             Stuid
---------------------------------------------------
92333444444     (1,6,7,10)
92333004444     (2,3)
92332334444     (4,5,9)

Re: multiples rows into one row. [message #675373 is a reply to message #675371] Sun, 24 March 2019 23:21 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
SQL> select
  2  p.mob,
  3      (select xmlagg(xmlelement(E, d.stuid || ',')).extract('//text()') AS SE
 from student d where d.mob = p.mob)
  4  from student p;

ERROR:
ORA-22922: nonexistent LOB value
WANT TO USE THIS QUERY IN REPORT BUILDER.
Re: multiples rows into one row. [message #675374 is a reply to message #675373] Mon, 25 March 2019 01:17 Go to previous messageGo to next message
Littlefoot
Messages: 21517
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Is this question about Forms (as suggested in topic info), or about Reports (as suggested by your words)?

Anyway: do as John suggested - create a view and use it in Forms and/or Reports. Your 6i version is very old, it isn't capable of doing a lot of things that database can.
Re: multiples rows into one row. [message #675376 is a reply to message #675374] Mon, 25 March 2019 07:20 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
i have created a view. but i found this after using it.
MOB	            SEE
923056733136	[datatype]
923042039519	[datatype]
923063375586	[datatype]
923063375586	[datatype]
923337663357	[datatype]
923337663357	[datatype]
923016301936	[datatype]

Quote:

CREATE OR REPLACE FORCE VIEW "MAS" ("MOB", "SEE") AS
select p.mob,
(select xmlagg(xmlelement(E, d.stuid || ',')).extract('//text()')
from student d where d.mob = p.mob) as see
from student p
/

[Updated on: Mon, 25 March 2019 07:21]

Report message to a moderator

Re: multiples rows into one row. [message #675379 is a reply to message #675376] Mon, 25 March 2019 09:10 Go to previous messageGo to next message
cookiemonster
Messages: 13752
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why don't you just use listagg?
Re: multiples rows into one row. [message #675380 is a reply to message #675379] Mon, 25 March 2019 09:14 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
listagg is not available in 10G XE.

Wm-concate not available
SYS_CONNECT_BY_PATH not available

please help to sort out.
Re: multiples rows into one row. [message #675381 is a reply to message #675380] Mon, 25 March 2019 09:35 Go to previous messageGo to next message
cookiemonster
Messages: 13752
Registered: September 2008
Location: Rainy Manchester
Senior Member
Upgrade your system to something sensible.

Are pipelined functions available in 10 XE?
Re: multiples rows into one row. [message #675382 is a reply to message #675381] Mon, 25 March 2019 14:02 Go to previous messageGo to next message
Littlefoot
Messages: 21517
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Create your own function. For such a simple task, the function is simple as well. For example:

SQL> select * From student;

       STUID NAME                           MOB
------------ ------------------------------ --------------------
           1 abc                            92333444444
           2 aac                            92333004444
           3 avc                            92333004444
           4 afc                            92333233444
           5 atc                            92333233444
           6 arc                            92333444444
           7 aec                            92333444444
           8 atc                            92333123444
           9 arc                            92332334444
          10 aec                            92333444444

10 rows selected.

SQL> create or replace function f_mob (par_mob in varchar2)
  2    return varchar2
  3  is
  4    retval varchar2(30);
  5  begin
  6    for cur_r in (select stuid from student
  7                  where mob = par_mob)
  8    loop
  9      retval := retval ||','|| cur_r.stuid;
 10    end loop;
 11
 12    retval := ltrim(rtrim(retval, ','), ',');
 13    return retval;
 14  end;
 15  /

Function created.

SQL> select f_mob('92333004444') one,
  2         f_mob('92333444444') two
  3  from dual;

ONE                  TWO
-------------------- --------------------
2,3                  1,6,7,10

SQL>
Re: multiples rows into one row. [message #675383 is a reply to message #675382] Mon, 25 March 2019 21:31 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
thanks for your help. i have done it with pivot query. but your query is more reliable.
Re: multiples rows into one row. [message #675393 is a reply to message #675383] Tue, 26 March 2019 08:32 Go to previous messageGo to next message
Littlefoot
Messages: 21517
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You're welcome. Doesn't really matter how you fix it, if it works OK. Most problems have more than one solution.
Re: multiples rows into one row. [message #675396 is a reply to message #675393] Tue, 26 March 2019 09:26 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And maybe he could help others, as he got helps in the last 17 years, posting his solution as asked in he the forum guide, but as he used to not even give feedback and/or thank people there are few chances this happens.

Re: multiples rows into one row. [message #677624 is a reply to message #675396] Tue, 01 October 2019 08:36 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
HERE IS ANOTHER ANSWER FOR Michel cadot


CREATE OR REPLACE TYPE t_string_agg AS OBJECT
(
  g_string  VARCHAR2(32767),

  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
                                       value  IN      VARCHAR2 )
     RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                     ctx2  IN      t_string_agg)
    RETURN NUMBER
);
/
SHOW ERRORS


CREATE OR REPLACE TYPE BODY t_string_agg IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)
    RETURN NUMBER IS
  BEGIN
    sctx := t_string_agg(NULL);
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
                                       value  IN      VARCHAR2 )
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := self.g_string || ',' || value;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER IS
  BEGIN
    returnValue := SUBSTR(SELF.g_string, 2);
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                     ctx2  IN      t_string_agg)
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := SELF.g_string || ctx2.g_string;
    RETURN ODCIConst.Success;
  END;
END;
/
SHOW ERRORS


CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg;
/
SHOW ERRORS
The aggregate function is implemented using a type and type body, and is used within a query.

COLUMN employees FORMAT A50

SELECT /*+ PARALLEL(2) */ deptno, string_agg(ename) AS employees
FROM   emp
GROUP BY deptno;

Re: multiples rows into one row. [message #677635 is a reply to message #677624] Tue, 01 October 2019 10:54 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK, you are able to copy and paste Tim Hall code (from a Tom Kyte one), would be better to post a link to their code.

Note that "g_string VARCHAR2(32767)" won't work in SQL as strings are limited to 4000 bytes.



Re: multiples rows into one row. [message #677639 is a reply to message #677635] Tue, 01 October 2019 22:22 Go to previous message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
but it woks for me.
Previous Topic: count stuid
Next Topic: Month Sequence in Matrix Report
Goto Forum:
  


Current Time: Mon Dec 09 07:13:33 CST 2019