[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


SQL Selet Join Question

Posted on 2011-04-25
Medium Priority
Last Modified: 2012-05-11
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
Question by:JT_SIRO
  • 2
LVL 41

Accepted Solution

ralmada earned 2000 total points
ID: 35462812
try this query
select distinct
	stuff((	select '/ ' + WriterName + ' ' + OwnershipPercent + ' ' + MusicOrg
		from SongWriters where fkRecID = a.RecID
		for xml path('')
	), 1, 2, '') 
from Songs a

Open in new window

LVL 41

Expert Comment

ID: 35462861
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
DECLARE @listStr varchar(2000)
SELECT @listStr = COALESCE(@listStr+' / ' ,'') + WriterName + ' ' + OwnershipPercent + ' ' + MusicOrg FROM SongWriters WHERE fkRecID = @Id
RETURN @listStr

Open in new window


Author Comment

ID: 35462920
That worked great!  Thanks a lot!

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question