Combining Subquery Results into a single field. 1

[NeilTrain]

OK, first off I would have written these queries completely different from the beginning but I didnt write the original code, and the front end interface is so complex that id rather retro-fit the query if i can, it would save a lot of time. I know it will end up sloppy but I have no choice.

We have this ASP page, pulls up 30 records, but instead of doing a join group by the programmer opened the first recordset and then called the database again for each record to get a subset of records, of course, this is a huge performance hit, because this page can have up to 150 independent calls to the DB server. But hes long gone and i am told to make this page work fast, and a total rewrite is out of the question, believe me I tried convincing them.

Anyways, what i need to do is get a subquery to pull a single column of no more than 5 records from another table and put them into a single varchar field of the parent record seperated by a comma. Is this possibe?

Thanks in advance

 

[carp]

Yes. The exact implementation will depend, of course, on which RDBMS you are using.

If you are using Oracle, you can create a function that accepts the parent value, pulls up the child values in a cursor, concatenates them into a comma delimited string, and returns the string.

Your query then becomes

SELECT parent_value, children_of(parent_value) FROM my_table;

 

[NeilTrain]

forgot to mention that, im using MS SQL Server 2000

 

[tlbroadbent]

Try this script as a starting place.

/* Declare and initialize a variable to hold the string */
Declare @var varchar(8000)
Set @var=''

/* The select statement concantenates the selected column from the first 5 rows @var */

Set rowcount 5
Select @var=@var + [columnname] + ','
From tablename
Where <criteria>

/* Get rid of last comma */
Set @var=left(@var,len(@var)-1)

/* Create record set */
Select OutputColumn=@var
Set rowcount 0 Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.