SQL Selet Join Question

Posted on 2011-04-25
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
    LVL 41

    Accepted Solution

    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

    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

    That worked great!  Thanks a lot!

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    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

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now