Return Multiple Values in SQL Subquery

liltyga
liltyga used Ask the Experts™
on
Hi,

I want to be able to return multiple values in a subquery, preferably in a comma delimited list.  I am using PostgreSQL and ColdFusion.  Is there a simple way to accomplish this?

For example, I want something like:

SELECT t.id, t.val2, t.val3,
   (SELECT s.id FROM state s WHERE t.id = s.id) as arrayOfStates
FROM table t

Any input would be greatly appreciated!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
I think the best way to do that is using a function that utilize a cursor to build the list of values string

Author

Commented:
how would you do that?
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

I think something like this can do the trick:

SELECT table.id[100], table.val2[100], table.val3[100]
    FROM table, state
    WHERE table.id=state.id

look at here for more info: http://www.postgresql.org/docs/8.2/interactive/arrays.html

Author

Commented:
Thanks, but I'm not familiar with creating and returning functions, so I wouldn't even know where to start with regard to writing one to return a comma delimited list of a value; that's why I posted my question here.
NerdsOfTechTechnology Scientist

Commented:
Can you give an example of data and the "desired output" of the data?

Thanks!

Author

Commented:
If I were more familiar with the direction given, I probably would have been able to figure out a solution, but I had to go with a workaround.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial