Derived Columns in one to many relationships

[Big Time]

I'm trying to write a query that concatenates multiple records into one
derived column. Let's say I have an author (Joe Writer) who has written
three books (Book 1, Book2 and Book 3). The author is in tblAuthors, his
books are in the tblBooks and they are joined by the AuthorID field
(number). If I use a simple select query to give me the author name and the
title, I will get three records, one for each book written.

What I want is to have all three books combined into one derived column. So
if I do the select statement, I will get one column with the author name,
and the second column will put together all three names of the book
separated by a column. So it will look like:

Author Title

Joe Writer Book 1, Book 2, Book 3,

Rather than having it appear as 3 records:

Joe Writer Book 1
Joe Writer Book 2
Joe Writer Book 3

Could someone help me with the SQL involved in this?

Thanks for the help.

Cheers,

Mike

[Douglas J. Steele]

One approach is shown in http://www.mvps.org/access/modules/mdl0008.htm at
"The Access Web"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Big Time" <bi************ @remove-for-spam-hotmail.com> wrote in message
news:cf******** ***@lettuce.bci t.ca...

I'm trying to write a query that concatenates multiple records into one
derived column. Let's say I have an author (Joe Writer) who has written
three books (Book 1, Book2 and Book 3). The author is in tblAuthors, his
books are in the tblBooks and they are joined by the AuthorID field
(number). If I use a simple select query to give me the author name and the title, I will get three records, one for each book written.

What I want is to have all three books combined into one derived column. So if I do the select statement, I will get one column with the author name,
and the second column will put together all three names of the book
separated by a column. So it will look like:

Author Title

Joe Writer Book 1, Book 2, Book 3,

Rather than having it appear as 3 records:

Joe Writer Book 1
Joe Writer Book 2
Joe Writer Book 3

Could someone help me with the SQL involved in this?

Thanks for the help.

Cheers,

Mike

[Pieter Linden]

Mike,

read this article...

http://www.mvps.org/access/modules/mdl0004.htm

It has code that does this.

[JK]

Try this out
DECLARE @BookNames varchar(1000)
SET @BookNames = ''
SELECT @BookNames = @BookNames +Book + ', ' FROM Books
where author = 'Joe Writer'
SELECT 'Joe Writer',@BookNa mes

[Erland Sommarskog]

JK (ja************ **@hotmail.com) writes:

Try this out
DECLARE @BookNames varchar(1000)
SET @BookNames = ''
SELECT @BookNames = @BookNames +Book + ', ' FROM Books
where author = 'Joe Writer'
SELECT 'Joe Writer',@BookNa mes

This may work. Or not work. The result of this sort of operation is
undefined in SQL Server. This is one of the few situations where
iterating over the data is a better option.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp