Have you tried distinct ? --- On Tue, 4/14/09, Ian Skinner <[email protected]> wrote:
From: Ian Skinner <[email protected]> Subject: Return|concatenate a list of values into one column To: "sql" <[email protected]> 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=89.70.6
