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

Would not this be a problem where recursive views could be of help? The shop
I work for is stuck at V5R3 but, at least from what I can see from the
manuals (V5R4 and on) it seems an UDF could be built around this, albeit
"with a lot of work"...

Regards,


Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries


On Wed, Apr 7, 2010 at 9:00 AM, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:

You might be able to write a UDF that would do it....

You can do a pivot...
Sub Type A B C D P Q
Type
1 X X X X
2 X X

Then maybe run the pivoted table through some field concatenations...

But since the i doesn't have a built-in pivot function, you have to
have a predefined set of values in sub-set to pivot on.


HTH,
Charles


On Wed, Apr 7, 2010 at 8:58 AM, Michael Naughton
<michael_naughton@xxxxxxxxxxxx> wrote:

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.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.