concatenating multiple rows into one record/column

[jgroove]

I am having a hard time writing queries after I have normalized my db to the 3nf, before I would place multiple values in a comma seperated list into one column, which made it impossible to work with the data. normailization has helped out tremendously in the application, but now i need to create reports from this tables and am having a very difficult time getting the recordsets that i need. i have multiple tables that i need to join, but i want it to return on record with a column having comma seperated values from the normalized tables. here is an example of what i am trying to do.

movie
------------
movie_id
title


movie_actor
-----------
movie_id
actor_id


actor
----------
actor_id
name


what i want as a result is

title actors
------ ---------
matrix keanu reeves,carrie-anne moss,laurence fishburne


i can not use a stored procedure, my application server does not handle ref_cursor as a return value. any information would be helpfull.

 

[blom0344]

Hello jgroove,

I stumbled on almost the same issue you are describing. Just using SQL will probably not get you anywere, cause without cursor or recordset this processing will not work.
I my case I was working on a linked table through an Access application which gave me an oppurtunity to use VBA. Being no VBA wizard I finally got a script through a forum which does the trick quit nicely. So , if you can live with Access as front end for reporting...... T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[Turkbear]

Hi,
You can use some scripting on a web page ( both ASP and JavaScript)
( Very rough guide follows
Get a record set that has
all actors names that match the movie_id and place them in an array...use the JavaScript arrayname.join(',') function to put them into a comma-separated string and use
document.write to display it or assign it to a form text item, or whatever..

 

[Mockenrue]

When you have SQL Server:

---------------------------------------------------------
7) TIP OF THE ISSUE: Return a SELECT list a CDS
---------------------------------------------------------
How often do you need to return a comma-delimited string
as the result of a SELECT statement? The following might
be useful and it doesn't use cursors.

DECLARE @PersonList varchar(100)
SELECT PersonID FROM Person

--Results--
1
2
4

Now alternatively
SELECT @PersonList = COALESCE(@PersonList + ', ', '') +
CAST(PersonID AS varchar)
FROM Person

--Results--
1, 2, 4


(from SQL Pro Tips, Trends & Technology eXTRA
Pinnacle Publishing

http://www.pinnaclepublishing.com/

Issue 113
October 8, 2002)