Inner select on one to many

Question

I have 2 tables event + event_artist

event

eventId | eventName  
-------------------
1 , gig1  
2, gig2

event_artist

eventId, artistName 
------------------- 
1, Led Zip  
1, The Beatles

ie Led Zep and the Beatles are both playing @ Gig1

I need to create the SQl to bind to a gridview ( you necessarily need to know about gridviews to answers this )

The results that i want would look like this
eventId = 1, EventName = Gig1. ArtistLineup = Led Zep, The beatles

So i need to create an alias ArtistLineup that would list all the artist. Via an inner select i think.

Any thoughts on what this would look like.

score 3 · Accepted Answer · edited Nov 17 '08 at 15:30

Saw this in SQL Server Magazine- not great, and the total list will have an upper length limit, but:

drop table event
go

drop table event_artist
go

create table event (eventid int, eventname varchar(255))
go

create table event_artist (eventid int, artistname varchar(255))
go

insert into event values (1, 'gig1')
go

insert into event values (2, 'gig2')
go

insert into event_artist values (1, 'Led Zip')
go

insert into event_artist values (1, 'The Beatles')
go

drop function Event_Display
go

create function Event_Display (@EventID int) returns varchar(2000) as
begin
    declare @artistList varchar(2000)
    set @artistList=''

    select @artistList=@artistList + ', ' + isnull(artistname,'') 
    from event_artist 
    where eventid=@EventID

    return substring(@artistList,3,2000)  --eliminate initial comma
end
go

select event.eventid, event.eventname, dbo.Event_Display(event.eventid) from event

                                                                                                                                                                                                                                                   
1           gig1        Led Zip, The Beatles

2           gig2

score 0 · Answer 2 · edited May 23 '17 at 10:32

0

SQL Server doesn't have anything built in to concatenate values in one statement like that. You could build the strings, but it has to be done one at a time.

However, you can get around this by building your own custom aggregate function (requires messy games with ActiveX objects in SQL Server 2000)

edited May 23 '17 at 10:32

Community

1
1

answered Nov 17 '08 at 14:59

Joel Coehoorn

399,467
113
570
794

score 0 · Answer 3 · edited May 23 '17 at 11:55

0

you might want to try something like this: Why does this SQL script work as it does?

edited May 23 '17 at 11:55

Community

1
1

answered Nov 17 '08 at 15:09

Kevin

7,162
11
46
70

score 0 · Answer 4 · edited Aug 05 '15 at 09:16

ScottK's answer is basically the one you want. Here's the rest of mine:

The query:

select e.*, dbo.ArtistList(e.EventId) as ArtistList
from [event] e

The function:

CREATE FUNCTION ArtistList<br>
(
    -- Add the parameters for the function here<br>
    @EventId int<br>
)
RETURNS varchar(MAX)<br>
AS
BEGIN
    -- Declare the return variable here
    DECLARE @ArtistList varchar(MAX)
    -- Add the T-SQL statements to compute the return value here
    SELECT @ArtistList = COALESCE(@ArtistList + ', ', '') + Artist
    FROM EventArtist
    WHERE EventId = @EventId
    -- Return the result of the function
    RETURN @ArtistList
END
GO

The only difference between my answer and ScottK's you might take note of is my use of varchar(MAX). That should pretty much address any concerns about the list of artists being truncated.

I've deleted my previous (incomplete) answer.

That only works for one item at a time. He'd have to run those in a cursor if he wants more than one result, which is less than desirable. — Joel Coehoorn, Nov 17 '08 at 15:31
Thanks for the comment. My revised answer addresses the issue you raised. — Scott Lawrence, Nov 17 '08 at 18:11

score 0 · Answer 5 · edited May 23 '17 at 12:34

You can use the clever FOR XML trick posted by Kevin Fairchild (I've modified it to take account of band names which will include spaces):

/*
create table [event] (eventid int, eventname varchar(255))
create table event_artist (eventid int, artistname varchar(255))
insert into [event] values (1, 'gig1')
insert into [event] values (2, 'gig2')
insert into event_artist values (1, 'Led Zip')
insert into event_artist values (1, 'The Beatles')
*/

SELECT  e.eventid
       ,e.eventname
       ,REPLACE(REPLACE(RTRIM((
                               SELECT   artistname + '| '
                               FROM     [event_artist]
                               WHERE    eventid = e.eventid
                              FOR
                               XML PATH('')
                              )), '| ', ', '), '|', '') AS artists
FROM    [event] AS e

Note that this requires columns in the FOR XML to be unnamed (named columns get an XML wrapper).

Inner select on one to many

event

event_artist

5 Answers5