Return|concatenate a list of values into one column

[Ian Skinner-3]

I need to do this in the database so the normal grouped cfoutput tags is
not going to work for me.

I need to return a list of values to a single column from a join, this
is in oracle 11g if that helps.

So instead of a normal joined record set that would look like this.

use_no  AI
1      A
1      B
2      C
3      D
4      E
4      F

I get a record set that looks like this:

use_no AI
1      A,B
2      C
3      D
4      E,F

I tried a Select sub-select, but that only works if the sub-select
returns a single row.

SELECT use_no, (SELECT AI FROM aTable b WHERE a.use_no = b.use_no)
FROM aTable a

This will fail with the following message.
SQL Error: ORA-01427: single-row subquery returns more than one row

I'm guessing I need some type of pivot query or something fancier, but
I'm unclear what, if anything, will allow this to work.




~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3210
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Maya Tulchinsky]

Have you tried distinct ?

--- On Tue, 4/14/09, Ian Skinner <HOF@...> wrote:

From: Ian Skinner <HOF@...>
Subject: Return|concatenate a list of values into one column
To: "sql" <sql@...>
Date: Tuesday, April 14, 2009, 9:52 AM


I need to do this in the database so the normal grouped cfoutput tags is
not going to work for me.

I need to return a list of values to a single column from a join, this
is in oracle 11g if that helps.

So instead of a normal joined record set that would look like this.

use_no  AI
1      A
1      B
2      C
3      D
4      E
4      F

I get a record set that looks like this:

use_no AI
1      A,B
2      C
3      D
4      E,F

I tried a Select sub-select, but that only works if the sub-select
returns a single row.

SELECT use_no, (SELECT AI FROM aTable b WHERE a.use_no = b.use_no)
FROM aTable a

This will fail with the following message.
SQL Error: ORA-01427: single-row subquery returns more than one row

I'm guessing I need some type of pivot query or something fancier, but
I'm unclear what, if anything, will allow this to work.






~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3211
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Ian Skinner-3]

Maya Tulchinsky wrote:
> Have you tried distinct ?

How would distinct turn:

1   A
1   B

Into

1 A,B

Rather then just make the second row disappear?


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3212
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Mike Hughes]

I make a list out of a column like this

DECLARE
        @theList varchar(max)

        SET @theList = ''

        SELECT @theList = @theList + Cast(Table.ID As varchar(7)) + ','
        FROM          Table
        WHERE     Status <> 'Discontinued'


        SET @theList = SUBSTRING(@theList, 1, Len(@theList) - 1)
--
PRINT @theList
--------------------------------------------------------------------------------------------
Mike Hughes                                      Michael.T.Hughes@...
--------------------------------------------------------------------------------------------



On Wed, Apr 15, 2009 at 4:09 PM, Ian Skinner <HOF@...> wrote:

>
> Maya Tulchinsky wrote:
>> Have you tried distinct ?
>
> How would distinct turn:
>
> 1   A
> 1   B
>
> Into
>
> 1 A,B
>
> Rather then just make the second row disappear?
>
>
>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3213
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6