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

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.