Home » SQL & PL/SQL » SQL & PL/SQL » concatenate field results
concatenate field results [message #192528] Tue, 12 September 2006 14:21 Go to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Is it possible to concatenate multiple results from the same field onto one line using SQL? For example, the data is in the table like so:


CUST_ID   CUST_NAME   PURCHASE   DATE

1234      DOE, JOHN   ABC        01/01/06
1234      DOE, JOHN   BCD        01/02/06
1234      DOE, JOHN   CDE        01/03/06
1234      DOE, JOHN   DEF        01/04/06

I would like the results to look like:

CUST_NAME   CUST_ID   PURCHASE

DOE, JOHN   1234      ABC, BCD, CDE, DEF


thanks,
Stan

[Updated on: Tue, 12 September 2006 14:22]

Report message to a moderator

Re: concatenate field results [message #192531 is a reply to message #192528] Tue, 12 September 2006 14:36 Go to previous messageGo to next message
shoblock
Messages: 325
Registered: April 2004
Senior Member
go to asktom.com and search for PIVOT
Re: concatenate field results [message #192551 is a reply to message #192528] Tue, 12 September 2006 18:35 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
An additional caveat to this is the PURCHASE field can be 'free formed' and has an infinite number of possibilities.

Thanks,
Stan
Re: concatenate field results [message #192556 is a reply to message #192551] Tue, 12 September 2006 19:29 Go to previous messageGo to next message
shoblock
Messages: 325
Registered: April 2004
Senior Member
I repeat, go to asktom.com and search for PIVOT. it's a method of puttinf rows into columns, when the number of values across is unknown/limitless
Re: concatenate field results [message #192559 is a reply to message #192528] Tue, 12 September 2006 20:45 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
It is my understanding that using PIVOT the results have to be known. Even though the number of values across can be unknown/limitless, the values still have to be known. Am I wrong?

Thanks,
Stan
Re: concatenate field results [message #192576 is a reply to message #192528] Tue, 12 September 2006 21:39 Go to previous messageGo to next message
shoblock
Messages: 325
Registered: April 2004
Senior Member
yes, you are wrong. GO TO ASKTOM.COM AND SEARCH!!!!!
Re: concatenate field results [message #192630 is a reply to message #192576] Wed, 13 September 2006 01:41 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Or stay here and search for pivot. In, fact CONCAT_ALL/STRAGG are the functions you are looking for. I favor CONCAT_ALL, since you can distinct/sort within your concatenation. It has been a long time since it has been posted here, I'll include the source:
--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;
/


It is a user-defined aggregate and the original author (J. Padfield) did post it at asktom.oracle.com, like shoblock hinted. I believe the map function (that allows the sort/distinct) is new in this variant though.

Test script:
CREATE TABLE mhe_foo(cust_id NUMBER, cust_name VARCHAR2(20), purchase VARCHAR2(3), cust_date DATE)
/
INSERT INTO mhe_foo VALUES (1234, 'DOE, JOHN', 'DEF', TO_DATE('01/04/2006','MM/DD/YYYY'));
INSERT INTO mhe_foo VALUES (1234, 'DOE, JOHN', 'ABC', TO_DATE('01/01/2006','MM/DD/YYYY'));
INSERT INTO mhe_foo VALUES (1234, 'DOE, JOHN', 'BCD', TO_DATE('01/02/2006','MM/DD/YYYY'));
INSERT INTO mhe_foo VALUES (1234, 'DOE, JOHN', 'CDE', TO_DATE('01/03/2006','MM/DD/YYYY'));
INSERT INTO mhe_foo VALUES (1234, 'DOE, JOHN', 'DEF', TO_DATE('01/04/2006','MM/DD/YYYY'));


COL purchases FORMAT A30

SELECT cust_name
     , cust_id
     , CONCAT_ALL(CONCAT_EXPR(purchase, ',')) purchases
FROM   mhe_foo
GROUP  BY cust_id, cust_name
/

SELECT cust_name
     , cust_id
     , CONCAT_ALL(DISTINCT CONCAT_EXPR(purchase, ',')) purchases
FROM   mhe_foo
GROUP  BY cust_id, cust_name
/

DROP TABLE mhe_foo
/


The nice thing is you can use it as a true aggregate function (PARTITION/ORDER BY)

MHE

[Updated on: Wed, 09 May 2007 06:17]

Report message to a moderator

Re: concatenate field results [message #192731 is a reply to message #192528] Wed, 13 September 2006 07:03 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
When you 'CREATE TABLE' the values must be known, hence my reply post to shoblock 'Even though the number of values across can be unknown/limitless, the values still have to be known' I'm dealing with a table that can have a half million records and CREATE TABLE in this manner is not an option. The field I'm wanting to concatenate can have limitless or infinite entries....Or is it I'm just totally missing how this works.?.?

Thanks,
Stan
Re: concatenate field results [message #192733 is a reply to message #192731] Wed, 13 September 2006 07:12 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Create table? Where did you mention that? What on earth are you trying to do? Look back at your "I would like the results to look like:" in your initial post. But it is true: concat_all will concatenate all the values as a single varchar2 column. A comma separated list in my example. If you want different columns, a search for pivot would show alternatives. Limits do apply, of course. Something like this.

MHE
Re: concatenate field results [message #192760 is a reply to message #192528] Wed, 13 September 2006 08:32 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I never said CREATE TABLE, you did with CREATE TABLE mhe_foo.
Re: concatenate field results [message #192763 is a reply to message #192760] Wed, 13 September 2006 08:39 Go to previous messageGo to next message
shoblock
Messages: 325
Registered: April 2004
Senior Member
for god's sake stan, try reading the messages. it was a frickin example. you didn't give table descriptions, so he made some up to show how the code works. now take the EXAMPLE code, and change it to fit YOUR TABLES!!!
Re: concatenate field results [message #192766 is a reply to message #192528] Wed, 13 September 2006 08:47 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I guess I'm not making myself clear. How am I to CREATE TABLE with a half million possible field results? Am I to do:

CREATE TABLE mhe_foo(cust_id NUMBER, cust_name VARCHAR2(20), purchase VARCHAR2(3), cust_date DATE)
/
INSERT INTO mhe_foo VALUES (1234, 'DOE, JOHN', 'DEF', TO_DATE('01/04/2006','MM/DD/YYYY'));
INSERT INTO mhe_foo VALUES (1234, 'DOE, JOHN', 'ABC', TO_DATE('01/01/2006','MM/DD/YYYY'));
INSERT INTO mhe_foo VALUES (1234, 'DOE, JOHN', 'BCD', TO_DATE('01/02/2006','MM/DD/YYYY'));
INSERT INTO mhe_foo VALUES (1234, 'DOE, JOHN', 'CDE', TO_DATE('01/03/2006','MM/DD/YYYY'));
INSERT INTO mhe_foo VALUES (1234, 'DOE, JOHN', 'DEF', TO_DATE('01/04/2006','MM/DD/YYYY'));

and INSERT INTO a half million times?

Try taking a chill pill shoblock. I'm just trying to learn.
Re: concatenate field results [message #192772 is a reply to message #192766] Wed, 13 September 2006 09:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I have to agree with @Shoblock here - it doesn't look like you're trying terribly hard.

If you'd run the example that @Maaher provided, you'd have seen that the table MHE_FOO exists simply to provide some test data to do the demonstration with.

If you'd provided a set of test data create table scripts, the he'd have used them, but you didn't.
Now that you've got a working example showing how to produce the result you need from a set of sample data, its up to you to apply this method to your data structure.



Re: concatenate field results [message #192773 is a reply to message #192528] Wed, 13 September 2006 09:11 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
OK....I got it now guys. Sorry for being so dense! I'm under a lot of pressure and am not thinking too clearly.

Thanks,
Stan
Re: concatenate field results [message #192872 is a reply to message #192773] Thu, 14 September 2006 00:53 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Laughing, now that was amusing. Is your problem solved, Stan? Note that the concat_all, concat_expr and concat_all_ot function and types should be created just once. Afterwards concat_all can be used like any other aggregate function. I ripped the source from one of our test databases where it is used in reporting I believe.

MHE
Re: concatenate field results [message #193107 is a reply to message #192766] Thu, 14 September 2006 22:28 Go to previous messageGo to next message
shoblock
Messages: 325
Registered: April 2004
Senior Member
gee stan, looks like you owe me an apology. I'm waiting...
Re: concatenate field results [message #193110 is a reply to message #193107] Thu, 14 September 2006 22:34 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Don't push it, shoblock. He did apologize.
Re: concatenate field results [message #193113 is a reply to message #193110] Thu, 14 September 2006 22:45 Go to previous messageGo to next message
shoblock
Messages: 325
Registered: April 2004
Senior Member
geez, I'm just joking around. can't we all just get along?
Re: concatenate field results [message #217294 is a reply to message #193113] Thu, 01 February 2007 09:29 Go to previous messageGo to next message
agostino_neto
Messages: 180
Registered: July 2005
Senior Member
Hi all,
I've only changed Maaher script to:
SELECT cust_name
, cust_id
, CONCAT_ALL(CONCAT_EXPR(purchase, ',')) purchases
, CONCAT_ALL(CONCAT_EXPR('car'||purchase, ',')) car_purchases
FROM mhe_foo
GROUP BY cust_id, cust_name

Can somebody tell me why the results appears on a different order between CONCAT_ALL(CONCAT_EXPR(purchase, ',')) and CONCAT_ALL(CONCAT_EXPR('car'||purchase, ',')) ?

The same thing appears with stragg function.
I am looking for a way to have the same order.
Thanks.
Re: concatenate field results [message #217742 is a reply to message #217294] Mon, 05 February 2007 03:57 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Let me take my original query and modify it a little bit:
SELECT cust_name
     , cust_id
     , CONCAT_ALL(CONCAT_EXPR(purchase, ',')) purchases
FROM   ( SELECT cust_name, cust_id, purchase
         FROM   mhe_foo
         ORDER  BY cust_name, cust_id, purchase ASC
       )
GROUP BY cust_name, cust_id
/


Have you looked at the PARTITION BY and ORDER BY clauses of analytic functions? CONCAT_ALL does support those clauses.

MHE
Previous Topic: Update Query
Next Topic: [ORA-04091] - Mutating table... Very,.very strange behavior!!!
Goto Forum:
  


Current Time: Fri Dec 09 09:37:02 CST 2016

Total time taken to generate the page: 0.22925 seconds