AW: SQL Question: Combine Several Rows Into One Column?

["Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>]

Hi,

you may write an UDF, something like this:

CREATE FUNCTION MySchema/MyUDF ( 
	PARType Integer ) 
	RETURNS VARCHAR(1024)   
	LANGUAGE SQL 
	NOT DETERMINISTIC 
	READS SQL DATA 
	CALLED ON NULL INPUT 
	DISALLOW PARALLEL 
BEGIN 
   DECLARE RETURNVAL VARCHAR (1024) NOT NULL DEFAULT ' ' ; 
  
   FOR CSRC1 AS C1 CURSOR 
       FOR SELECT SubType 
              FROM TypeTable 
              WHERE Type = ParType 
       DO SET RETURNVAL = RETURNVAL CONCAT ' ' CONCAT CSRC1.SubType; 
   END FOR ; 
  
   RETURN LTRIM ( RETURNVAL ) ; 
END;

This UDF can be called as follows:

Select Distinct Type, MyUDF(Type)
> From TypeTable;

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Michael Naughton
Gesendet: Wednesday, 07. April 2010 14:59
An: Midrange Systems Technical Discussion
Betreff: SQL Question: Combine Several Rows Into One Column?

I think I know the answer to this, but I wanted to see if there's something
I've missed. Suppose I have the following table:

Type    Sub-Type
 1          A
 1          B
 1          C
 1          D
 2          P
 2          Q

Is there a way to use SQL to produce the following result:

Type     Sub-Types
   1         ABCD
   2         PQ

The idea is to somehow concatenate all the sub-types for a given type into
one column, given that a type may have any number of sub-types.

I suspect the answer is "no", or at least "not without a lot of work". Am I
missing something?

Thanks very much,


 

Mike Naughton
Senior Programmer/Analyst
Judd Wire, Inc.
124 Turnpike Road
Turners Falls, MA  01376
413-676-3144
Internal: x 444
mnaughton@xxxxxxxxxxxx
****************************************
NOTICE: This e-mail and any files transmitted with it are confidential and
solely for the use of the intended recipient. If you are not the intended
recipient or the person responsible for delivering to the intended
recipient, be advised that any use is
strictly prohibited. If you have received this e-mail in error, please
notify us immediately by replying to it and then delete it from your
computer.