Home » SQL & PL/SQL » SQL & PL/SQL » merging multiple rows
icon5.gif  merging multiple rows [message #221368] Mon, 26 February 2007 08:18 Go to next message
kuchp
Messages: 7
Registered: February 2007
Location: Germany
Junior Member
Dear all,

could please someone help me with this:

I've two tables:

1. table one, called subprj is like
project_id project_title structure project_profile
---------- ------------- --------- ---------------
1 project one single P
2 project two single C
3 project three multi D
... ... ... ...

2. table two, calles sprjcategory is like

project_id category
---------- --------
1 Application Development
1 Exploratory Research
2 Custom Advisory
3 Exploratory Research

If I use a SQL-Statement to ask for all categories of a certain project I have no problem but my result leads me to have multiple rows for one project like

project_id project_title category
---------- ------------- --------
1 project one Application Development
1 project one Exploratory Research

Is there any possibility in SQL to come to the following result:

project_id project_title category
---------- ------------- --------
1 project one Application Development,
Exploratory Research

This means I want to have only one row per project_id and merge the data of column category with multiple values, seperated by ','. Is this possible and if yes, how???

I would appreciate any kind of help. Thanks in advance
regards kuchp

Re: merging multiple rows [message #221371 is a reply to message #221368] Mon, 26 February 2007 08:24 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Search the board for "concat_all".

MHE
Re: merging multiple rows [message #221372 is a reply to message #221371] Mon, 26 February 2007 08:27 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Or check this link
Re: merging multiple rows [message #221392 is a reply to message #221371] Mon, 26 February 2007 09:43 Go to previous messageGo to next message
kuchp
Messages: 7
Registered: February 2007
Location: Germany
Junior Member
Dear Maaher,
thanks for your kind answer but there are some problems left.
I understand that I have to create thoses types to make the concat_all function do the work for the solution I need.
But for the reason i am really not adept at PL/SQL I took the source code as it was and copied it into my database.
The concat_expr type has been created very well. But there are some problems with the concat_al_ot type.
Oracle says the type is incomplete and it gives the PLS-00382 error: expression is of wrong type. I work with oracle 9i.
What did I do wrong?
I am really ashamed to bother you like this, but I have to solve this exasperating problem.
Thanks for your kind help
Regard kuchp
Re: merging multiple rows [message #221395 is a reply to message #221392] Mon, 26 February 2007 09:55 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
In cases like this it would be nice if you gave us some details:
- Oracle version (select banner from v$version)
- the actual error message.
A copy/paste from SQL/Plus would do just fine (don't forget code tags).
I can't guess what went wrong. On a 9iR2 it should give no problems. It is installed on 9iR2 in this thread. I've got no 9iR1 at my disposal but in that case, you could have a look at the link tahpush posted. In my experience it is not the fastest solution but it can do the trick.

MHE
Re: merging multiple rows [message #221406 is a reply to message #221395] Mon, 26 February 2007 10:35 Go to previous messageGo to next message
kuchp
Messages: 7
Registered: February 2007
Location: Germany
Junior Member
Dear Maaher,

sorry to bother you again.
Please see enclodes file for details.
I will check the message of tahpush meanwhile.
Thank you
Kind regards
kuchp
Re: merging multiple rows [message #221414 is a reply to message #221372] Mon, 26 February 2007 10:58 Go to previous messageGo to next message
kuchp
Messages: 7
Registered: February 2007
Location: Germany
Junior Member
Dear Tahpush,

thanks for your ind replay.
I tried this one out.
I created a view from both of my table just to work with only one "table" and then tried the SQL-statement from your link.

Oracle gives me the error: ORA-01489: result of string concatenation is too long.

Can you tell me the limitation for this?

Thanks and kind regards kuchp
Re: merging multiple rows [message #221498 is a reply to message #221414] Tue, 27 February 2007 03:43 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Ok,
I gave your file a chance. Note that it is absolutely not my habit to download files from here. It's more efficient to simply embed the code in your message. I immediately saw that you didn't try your statements yourself. They contain a lot of typo's and syntax errors.

I modified it, formatted it and here's the result:
SELECT banner 
FROM   v$version
/

CREATE TABLE subprj
(
  project_id VARCHAR2 (12) NOT NULL,
  project_title VARCHAR2 (20),
  STRUCTURE VARCHAR2 (10),
  project_profile CHAR (1)
)
/

ALTER TABLE SUBPRJ ADD
(
  CONSTRAINT SUBPRJ_PK
  PRIMARY KEY
  (
PROJECT_ID
  )
)
/

INSERT INTO subprj
            (project_id
           , project_title
           , STRUCTURE
           , project_profile
            )
VALUES      ('1'
           , 'project one'
           , 'single'
           , 'P'
            )
/

INSERT INTO subprj
            (project_id
           , project_title
           , STRUCTURE
           , project_profile
            )
VALUES      ('2'
           , 'project two'
           , 'single'
           , 'C'
            )
/

INSERT INTO subprj
            (project_id
           , project_title
           , STRUCTURE
           , project_profile
            )
VALUES      ('3'
           , 'project three'
           , 'multi'
           , 'D'
            )
/

CREATE TABLE sprjcategory
(
  sprjcat_id VARCHAR2 (12) NOT NULL,
  project_id VARCHAR2 (12) NOT NULL,
  CATEGORY VARCHAR2 (255)
)
/

ALTER TABLE sprjcategory ADD
(
  CONSTRAINT sprjcategory_pk
  PRIMARY KEY
  (
    sprjcat_id
  )
)
/

ALTER TABLE sprjcategory ADD
(
  CONSTRAINT sprjcategory_subprj_fk
  FOREIGN KEY
  (
    project_id
  )
  REFERENCES subprj
  (
    project_id
  )
  ON DELETE CASCADE
  NOT DEFERRABLE INITIALLY IMMEDIATE
  ENABLE
)
/

INSERT INTO sprjcategory
            (sprjcat_id
           , project_id
           , CATEGORY
            )
VALUES      ('1'
           , '1'
           , 'Application Development'
            )
/

INSERT INTO sprjcategory
            (sprjcat_id
           , project_id
           , CATEGORY
            )
VALUES      ('2'
           , '1'
           , 'Exploratory Research'
            )
/

INSERT INTO sprjcategory
            (sprjcat_id
           , project_id
           , CATEGORY
            )
VALUES      ('3'
           , '2'
           , 'Custom Advisory'
            )
/

INSERT INTO sprjcategory
            (sprjcat_id
           , project_id
           , CATEGORY
            )
VALUES      ('4'
           , '3'
           , 'Exploratory Research'
            )
/

col description format a50
SELECT subprj.project_id
     , subprj.project_title
     , concat_all (concat_expr (sprjcategory.CATEGORY, ',')) description
FROM   subprj
   ,   sprjcategory
WHERE  subprj.project_id = sprjcategory.project_id
GROUP  BY subprj.project_id
        , subprj.project_title
/

DROP TABLE sprjcategory
/

DROP TABLE subprj
/
I connected to one of our test 9i databases. This database had the CONCAT_ALL already installed, using this script (I add the code so that there is no misunderstanding):
--CONCAT_EXPR 

CREATE OR REPLACE TYPE "CONCAT_EXPR" AS OBJECT (
   str   VARCHAR2 (4000)
 , del   VARCHAR2 (4000)
 , MAP MEMBER FUNCTION f_map
      RETURN VARCHAR2
);
/

CREATE OR REPLACE TYPE BODY concat_expr
AS
   MAP MEMBER FUNCTION f_map
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN str;
   END f_map;
END;
/

--CONCAT_ALL_OT 

CREATE OR REPLACE TYPE "CONCAT_ALL_OT" AS OBJECT (
   str   VARCHAR2 (4000)
 , del   VARCHAR2 (4000)
 , STATIC FUNCTION odciaggregateinitialize (sctx IN OUT concat_all_ot)
      RETURN NUMBER
 , MEMBER FUNCTION odciaggregateiterate (
      SELF   IN OUT   concat_all_ot
    , ctx    IN       concat_expr
   )
      RETURN NUMBER
 , MEMBER FUNCTION odciaggregateterminate (
      SELF          IN       concat_all_ot
    , returnvalue   OUT      VARCHAR2
    , flags         IN       NUMBER
   )
      RETURN NUMBER
 , MEMBER FUNCTION odciaggregatemerge (
      SELF   IN OUT   concat_all_ot
    , ctx2            concat_all_ot
   )
      RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY concat_all_ot
AS
   STATIC FUNCTION odciaggregateinitialize (sctx IN OUT concat_all_ot)
      RETURN NUMBER
   IS
   BEGIN
      sctx    := concat_all_ot (NULL, NULL);
      RETURN odciconst.success;
   END;
   MEMBER FUNCTION odciaggregateiterate (
      SELF   IN OUT   concat_all_ot
    , ctx    IN       concat_expr
   )
      RETURN NUMBER
   IS
   BEGIN
      IF SELF.str IS NOT NULL
      THEN
         SELF.str    := SELF.str || ctx.del;
      END IF;

      SELF.str    := SELF.str || ctx.str;
      RETURN odciconst.success;
   END;
   MEMBER FUNCTION odciaggregateterminate (
      SELF          IN       concat_all_ot
    , returnvalue   OUT      VARCHAR2
    , flags         IN       NUMBER
   )
      RETURN NUMBER
   IS
   BEGIN
      returnvalue    := SELF.str;
      RETURN odciconst.success;
   END;
   MEMBER FUNCTION odciaggregatemerge (
      SELF   IN OUT   concat_all_ot
    , ctx2   IN       concat_all_ot
   )
      RETURN NUMBER
   IS
   BEGIN
      IF SELF.str IS NOT NULL
      THEN
         SELF.str    := SELF.str || SELF.del;
      END IF;

      SELF.str    := SELF.str || ctx2.str;
      RETURN odciconst.success;
   END;
END;
/

CREATE OR REPLACE FUNCTION concat_all (
  ctx IN concat_expr)
  RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE
  AGGREGATE USING concat_all_ot;
/


Now I've got it all in place, I ran the script. This is an exact copy/paste from SQL*Plus, no fiddling:
SQL> @orafaq

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
PL/SQL Release 9.2.0.7.0 - Production
CORE    9.2.0.7.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production


Table created.


Table altered.


1 row created.


1 row created.


1 row created.


Table created.


Table altered.


Table altered.


1 row created.


1 row created.


1 row created.


1 row created.


PROJECT_ID   PROJECT_TITLE
------------ --------------------
DESCRIPTION
--------------------------------------------------
1            project one
Application Development,Exploratory Research

2            project two
Custom Advisory

3            project three
Exploratory Research



Table dropped.


Table dropped.

SQL>


To conclude: with the simplified script I distilled from your attachment, I had no problems.

MHE
Re: merging multiple rows [message #221580 is a reply to message #221498] Tue, 27 February 2007 09:28 Go to previous messageGo to next message
kuchp
Messages: 7
Registered: February 2007
Location: Germany
Junior Member
Dear Maaher,
just to keep you informed:
really thanks a lot. I tried everything out and it works properly.
I really appreciate your help very much.
Kind regards
kuchp

Re: merging multiple rows [message #221692 is a reply to message #221580] Wed, 28 February 2007 01:45 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Thanks for the update.

http://www.orafaq.com/forum/fa/448/0/

Closed.

MHE
Previous Topic: delete characters from column
Next Topic: How to code this?
Goto Forum:
  


Current Time: Fri Dec 09 02:15:24 CST 2016

Total time taken to generate the page: 0.05291 seconds