Inner select on one to many

I have 2 tables event + event_artist

event
eventId | eventName
1 , gig1
2, gig2

  • What does sp_reset_connection do?
  • Workarounds for ReadUncommitted Isolation level in an SSIS package
  • SQL Server 2005 Memory Pressure and tempdb writes problem
  • Trouble with SQL syntax
  • When increasing the size of VARCHAR column on a large table could there be any problems?
  • SQL get ROW_NUMBER and COUNT on every SELECT request
  • 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.

  • How can I do multiple inserts without a cursor
  • Returning NULLs in SQL if joined table is missing records for that Date
  • Simple, ordinary SQL Server insert method
  • SQL Server, Converting NTEXT to NVARCHAR(MAX)
  • Retrieve column names from a different table?
  • Difference between database level trigger and server level trigger in SQL Server
  • 5 Solutions collect form web for “Inner select on one to many”

    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     
    

    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)

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

    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).

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.