内部选择一对多

I have 2 tables event + event_artist

我有2个表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

即Led Zep和甲壳虫乐队都在玩@ Gig1

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

我需要创建SQl以绑定到gridview(你必须知道gridviews来解答这个问题)

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

我想要的结果看起来像eventId = 1,EventName = Gig1。 ArtistLineup = Led Zep,披头士乐队

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

所以我需要创建一个别名ArtistLineup,列出所有艺术家。通过内心选择我认为。

Any thoughts on what this would look like.

关于这会是什么样子的任何想法。

5 个解决方案

#1

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

在SQL Server Magazine中看到这个 - 不是很好,总列表将有一个上限,但是:

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

#2

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.

SQL Server没有内置任何内容来连接一个语句中的值。你可以构建字符串,但必须一次完成一个。

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

但是,您可以通过构建自己的自定义聚合函数来解决这个问题(需要在SQL Server 2000中使用ActiveX对象进行混乱的游戏)

#3

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

您可能想尝试这样的事情:为什么这个SQL脚本会像它一样工作?

#4

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

您可以使用Kevin Fairchild发布的聪明的FOR XML技巧(我已修改它以考虑包含空格的乐队名称):

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

请注意,这要求FOR XML中的列未命名(命名列获取XML包装器)。

#5

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

ScottK的答案基本上就是你想要的答案。这是我的其余部分:

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.

我可以注意到的答案和ScottK之间的唯一区别是我使用varchar(MAX)。这应该可以解决有关被截断的艺术家名单的任何问题。

I've deleted my previous (incomplete) answer.

我删除了之前的(不完整的)答案。

#1