grouping records

Hi,
I've got three tables:
[Article]{ArticleID, Title}
[Journalist]{JournalistID, Name}
[ArticleJournalist]{JournalistID, ArticleID}
One article can be associated to one or more journalist.
Here's my request to get all of the records:
SELECT A.Title, A.ArticleID, J.JournalistID, J.Name
FROM Articles A INNER JOIN JournalistArticle JA ON A.ArticleID =
JA.ArticleID
INNER JOIN Journalist JON JA.JournalistID = J.JournalistID
It works fine but obviously there are duplicates in the resultset. How
can I store that in a temp table and have a column that would contain
the journalist's names comma-separated ?
ThanksAvoid having multiple values in a single column. It has obvious drawbacks
with respect to constraint enforcements and increased complexity.
Please search the archives of this newsgroup for a variety of workarounds.
For instance:
( For SQL 2005 only )
http://groups.google.com/group/micr...br />
9b9b968a
( For SQL 2000 & 2005 )
http://groups.google.com/group/micr...br />
6dd9e73e
Anith|||" journalist's names comma-separated "
You are violating "First Normal Form" with this.
..
Do NOT violate First Normal Form. It is always a bad/stupid/idiotic idea.
http://databases.about.com/cs/speci...ducts/g/1nf.htm
<samuelberthelot@.googlemail.com> wrote in message
news:1148310249.207864.134000@.j73g2000cwa.googlegroups.com...
> Hi,
> I've got three tables:
> [Article]{ArticleID, Title}
> [Journalist]{JournalistID, Name}
> [ArticleJournalist]{JournalistID, ArticleID}
> One article can be associated to one or more journalist.
> Here's my request to get all of the records:
> SELECT A.Title, A.ArticleID, J.JournalistID, J.Name
> FROM Articles A INNER JOIN JournalistArticle JA ON A.ArticleID =
> JA.ArticleID
> INNER JOIN Journalist JON JA.JournalistID = J.JournalistID
> It works fine but obviously there are duplicates in the resultset. How
> can I store that in a temp table and have a column that would contain
> the journalist's names comma-separated ?
> Thanks
>|||I assume you want to do this for display reasons, and that you have no
intent of actually storing comma seperated values in a table. Storing the
data in that format would cause all sorts of problems.
Although there are several ways to do this in SQL, either with cursors or
XML (on 2005), this is something usually done in your presentation layer,
rather than on the database side. See if your application/reporting tool
can handle this. If you absolutley insist on doing this with SQL, there are
a number of solutions easily found by a quick search.
<samuelberthelot@.googlemail.com> wrote in message
news:1148310249.207864.134000@.j73g2000cwa.googlegroups.com...
> Hi,
> I've got three tables:
> [Article]{ArticleID, Title}
> [Journalist]{JournalistID, Name}
> [ArticleJournalist]{JournalistID, ArticleID}
> One article can be associated to one or more journalist.
> Here's my request to get all of the records:
> SELECT A.Title, A.ArticleID, J.JournalistID, J.Name
> FROM Articles A INNER JOIN JournalistArticle JA ON A.ArticleID =
> JA.ArticleID
> INNER JOIN Journalist JON JA.JournalistID = J.JournalistID
> It works fine but obviously there are duplicates in the resultset. How
> can I store that in a temp table and have a column that would contain
> the journalist's names comma-separated ?
> Thanks
>|||Make no mistake: result sets are different from tables. Result sets
consist of records and fields, not of rows and columns. Rules of
normalization do not apply to result sets.