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

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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.