Concatenate result set into a string SQL
652479Mar 4 2009 — edited Mar 4 2009Hi, I need some help with concatenating a resultset to avoid duplication of data.
I have 3 tables:
SCRIPT
ID - Number PK
DATE - Date
TITLE - Varchar2
AUTHOR
A_ID - Number PK
A_Name - Varchar2
SCRIPT_AUTHOR
ID - Number - PK, FK to Script.ID
A_ID - Number PK, FK to Author.A_ID
I need to list all authors for each script on one line, at the moment I have the following SQL :
Select S.Title, SA.A.ID
FROM SCRIPT S, SCRIPT_AUTHOR SA
WHERE SCRIPT.ID = SCRIPT_AUTHOR.ID
and as expected this is returning a dataset as follows - giving me 2 lines in this case for one script :
S.TITLE, SA.A_ID
1, 1
1, 2
What I am actually after is a result set that combines both the authors in this case in one record: 1, 1:2 so I only have 1 line per script.
The scripts can have many authors, and I will want to do this for many scripts at a time.
I have hit a blank on how to do this, is it possible to do this with SQL?
Many Thanks
p.s. Using: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0