Internal choice one by one for many
I have 2 tables event + event_artist
eventId event | event_name
1, gig1
2, gig2
event_artist
eventId, artistName
1, Led Zip
1, The Beatles
those. Led Zep and the Beatles both play @ Gig1
I need to create an SQl to bind to a gridview (you definitely need to know about gridviews to answer this)
The results I want will look like this: eventId = 1, EventName = Gig1. ArtistLineup = Led Zep, The beatles
So I need to create an ArtistLineup alias that will list the entire artist. With inner choice, I think.
Any thoughts on how this will look.
source to share
Seeing this in the SQL Server log is not very good, and the general 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
source to share
SQL Server has nothing built in to concatenate values in one of these statements. You can create strings, but you have to do it one at a time.
However, you can work around this by creating your own custom aggregation function (requires messing around with ActiveX objects in SQL Server 2000)
source to share
you can try something like this: Why does this SQL script work the same as it does?
source to share
ScottK's answer is basically what you want. Here's the rest of mine:
Request:
select e.*, dbo.ArtistList(e.EventId) as ArtistList
from [event] e
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 you can take note of is the use varchar(MAX)
. This should pretty much solve any problems associated with reducing the list of artists.
I deleted my previous (incomplete) answer.
source to share
You can use the clever FOR XML trick posted by Kevin Fairchild (I changed it to take into account group names, which will contain 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 XML FOR columns to be unnamed (named columns are wrapped in XML).
source to share