Re: Concat a column?

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Tue, 15 Sep 2009 07:22:19 -0700 (PDT)
Message-ID: <9443b203-53f3-4b5f-9e79-1cade14b169e_at_f33g2000vbm.googlegroups.com>



On Sep 15, 9:22 am, Sashi <small..._at_gmail.com> wrote:
> On Sep 14, 2:51 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
>
>
>
>
>
> > "Sashi" <small..._at_gmail.com> a écrit dans le message de news: c18a563e-94cf-4f35-8c96-df4d40105..._at_j19g2000vbp.googlegroups.com...
> > | Hi All, I'm looking to concatenate multiple values for a single column
> > | and am wondering if this is possible.
> > |
> > | select concat(a1), a2, a3, a4
> > | from A
> > | group by a2,a3,a4
> > |
> > | The above will select multiple values of a1. These could range between
> > | one value to 10 or 12.
> > | I'd like to have them all concatenated, while retaining distinct
> > | values of a2,a3 and a4.
> > |
> > | Is this possible?
> > |
> > | TIA,
> > | Sashi
>
> > Search for stragg on AskTom site.
>
> > Regards
> > Michel
>
> Thanks, Michel.
> I've discovered that 10g offers a new function called collect which
> seems to work similar to the stragg. Here' the link:http://www.oracle-developer.net/display.php?id=306
>
> Regards,
> Sashi- Hide quoted text -
>
> - Show quoted text -

Interesting new function. I will have to check it out especially to see if under 10.2 where it creates the type under the executing user instead of user sys to see if the feature works when the user does not have create type privilege.

You can also solve this problem for character columns using a select that concatenates the columns returned by a series of analytic inline views outer joined together where each view provides the rows for one position of the result per partition key. That is view1 returns column1, view2 return column2, etc ... The row_number function can be used to order the data by key marking which row is col1, col2, col3, etc.... The concatenation puts all the columns into one result set column. You could then join this back to the base table to pick up other columns of interest.

HTH -- Mark D Powell -- Received on Tue Sep 15 2009 - 09:22:19 CDT

Original text of this message