asked on

SQL Selet Join Question

Hello - I have a relational database structure where I have a Songs table and a SongWriters table, that have a one-to-many relationship, joined by Songs.RecID = SongWriters.fkRecID.

I need to export to Excel in a Flat data structure. IE, I need to put all of the SongWriters for each song, into one SongWriters field. Does someone know the SQL to do this?

For example:

This Songs record:
RecID = 10, SongName = 'Let It Be'

need to join with these SongWriters table recs:
fkRecID = 10, WriterName = 'Jon Lenon', MusicOrg = 'ASCAP', OwnershipPercent = '25'
fkRecID = 10, WriterName = 'Paul McCartney', MusicOrg = 'ASCAP', OwnershipPercent = '25'
fkRecID = 10, WriterName = 'Ringo Star', MusicOrg = 'ASCAP', OwnershipPercent = '25'
fkRecID = 10, WriterName = 'George Harrison', MusicOrg = 'ASCAP', OwnershipPercent = '25'

I would want to put all of the above info into one field, delineated by a "/", if that's possible?

So my result set would look like:
RecID SongName Writers
-------- ------------- ---------------------------------------------------------------------------------
10 Let it Be Jon Lenon 25% ASCAP / Paul McCartney 25% BMI / Ringo Star 25% BMI / George Harrison 25% SESAC

ASKER CERTIFIED SOLUTION

ralmada

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ralmada

If you're in SQL 2000 then you can create the function attached below and then use it in your query like this:

select distinct RecID, SongName, dbo.fn_list_str(RecID) from Songs

CREATE FUNCTION [dbo].[fn_List_str](@Id int) RETURNS varchar(2000) AS
BEGIN
DECLARE @listStr varchar(2000)
SELECT @listStr = COALESCE(@listStr+' / ' ,'') + WriterName + ' ' + OwnershipPercent + ' ' + MusicOrg FROM SongWriters WHERE fkRecID = @Id
RETURN @listStr
END

Open in new window

JT_SIRO

ASKER

That worked great! Thanks a lot!