quote:

---

Originally posted by CDeBenedetto

Where to start...

Table article_author:
[id] [article_id] [author]
1 11 author 1
2 12 author 2
3 13 author 3
4 13 another author 3

Table article:
[id] [short_title] [issue_date] [ce_link]
11 title 1 March 2009 link 1
12 title 2 February 2009 link 2
13 title 3 January 2009 link 3

I made a proc to get all the articles and authors:

select article.id, short_title, issue_date, ce_link,
authors.author
from article as article
INNER JOIN article_author authors ON authors.article_id = article.id
where isnull(short_title, '') <> ''
order by article.id desc

The problem is I'm getting back:
[id] [short_title] [issue_date] [ce_link] [author]
13 title 3 January 2009 link author 3
13 title 3 January 2009 link another author 3
12 title 2 February 2009 link author 2
11 title 1 March 2009 link author 1

Is there a way to put both authors with the same id into one row? I created a proc to do this, but I don't know if there is a way I can join it:

DECLARE @str VARCHAR(max)
SELECT @str = COALESCE(@str + ' and ', '') + [author]
FROM article_author
where article_id = @article_id
select [author] = @str

returns:
author 3 and another author 3

I hope I'm explaining my dilemma correctly...

Thanks,
Chris

---