newbie: to cursor or not to cursor

Hi all, I'd like to ask a general question about some SQL query strategies.
Let's say I have 2 tables, e.g. an authors table (TAuthor), a works table
(TWork), and a work-authors table linking them (TWorkAuthor); each work may
have 1 or more authors:
TAuthor
authorID (PK)
firstName
lastName
TWork
workID (PK)
title
..
TWorkAuthor
wkauID (PK)
authorID
workID
Now I'd want to display a (readonly) view showing all the works with their
authors, but I do not want to make a join as I'd obtain multiple rows for
the same work when the work has more than 1 author, e.g.:
Mickey Mouse | A work by two authors | ...
Donald Duck | A work by two authors | ...
Uncle Scrooge | Another different work | ...
Rather I'd like to have:
Donald Duck, Mickey Mouse | A work by two authors | ...
Uncle Scrooge | Another different work | ...
I was thinking to create a function which uses a cursor to retrieve all the
author names for a specified @.workID, like (I retrieve only the last name
for brevity):
...
DECLARE author_cursor CURSOR FAST_FORWARD
FOR SELECT TAuthor.lastName FROM TAuthor
INNER JOIN TWorkAuthor
ON TAuthor.authID = TWorkAuthor.authID
WHERE TWorkAuthor.workID = @.workID
ORDER BY lastName
OPEN author_cursor
FETCH NEXT FROM author_cursor INTO @.name
WHILE @.@.FETCH_STATUS = 0 BEGIN
IF LEN(@.list) > 0 SET @.list = @.list + ', '
SET @.list = @.list + @.name
FETCH NEXT FROM author_cursor INTO @.name
END
CLOSE author_cursor
DEALLOCATE author_cursor
Then in my view query I could call this function for each work of my SELECT
statement to obtain a single row field with all its author names. Anyway I
fear such an approach might severely degrade the query performance. Is there
a better way of doing this or I'm going the right path?
Thanks to all in advance!Concatenate the author names client-side since this is purely a
presentational matter. In general it should be much easier to do in
languages other than SQL. Alternatively, if you're determined on the SQL
route you can Google in this group on "aggregation" and "concatenation" to
find some TSQL-based solutions.
David Portas
SQL Server MVP
--|||You mean you want all works with their authors but want a list of authors
where there ae multiples?
As to cursors see
http://www.mindsdoor.net/SQLTsql/Cursors.html
But a function could be useful as in
http://www.mindsdoor.net/SQLTsql/CS...bleEntries.html
something like
create function csvtbl
(@.id as int)
returns varchar(1000)
AS
begin
declare @.csv varchar(1000)
select @.csv = coalesce(@.csv+',','') + a.lastName
from TAuthor a
join TWorkAuthor wa
on a.authorID = wa.authorID
where wa.wkauID = @.id
return @.csv
end
go
then
select title, dbo.csvtbl(workID)
from TWork
"Dan" wrote:

> Hi all, I'd like to ask a general question about some SQL query strategies
.
> Let's say I have 2 tables, e.g. an authors table (TAuthor), a works table
> (TWork), and a work-authors table linking them (TWorkAuthor); each work ma
y
> have 1 or more authors:
> TAuthor
> authorID (PK)
> firstName
> lastName
> TWork
> workID (PK)
> title
> ...
> TWorkAuthor
> wkauID (PK)
> authorID
> workID
> Now I'd want to display a (readonly) view showing all the works with their
> authors, but I do not want to make a join as I'd obtain multiple rows for
> the same work when the work has more than 1 author, e.g.:
> Mickey Mouse | A work by two authors | ...
> Donald Duck | A work by two authors | ...
> Uncle Scrooge | Another different work | ...
> Rather I'd like to have:
> Donald Duck, Mickey Mouse | A work by two authors | ...
> Uncle Scrooge | Another different work | ...
> I was thinking to create a function which uses a cursor to retrieve all th
e
> author names for a specified @.workID, like (I retrieve only the last name
> for brevity):
> ...
> DECLARE author_cursor CURSOR FAST_FORWARD
> FOR SELECT TAuthor.lastName FROM TAuthor
> INNER JOIN TWorkAuthor
> ON TAuthor.authID = TWorkAuthor.authID
> WHERE TWorkAuthor.workID = @.workID
> ORDER BY lastName
> OPEN author_cursor
> FETCH NEXT FROM author_cursor INTO @.name
> WHILE @.@.FETCH_STATUS = 0 BEGIN
> IF LEN(@.list) > 0 SET @.list = @.list + ', '
> SET @.list = @.list + @.name
> FETCH NEXT FROM author_cursor INTO @.name
> END
> CLOSE author_cursor
> DEALLOCATE author_cursor
> Then in my view query I could call this function for each work of my SELEC
T
> statement to obtain a single row field with all its author names. Anyway I
> fear such an approach might severely degrade the query performance. Is the
re
> a better way of doing this or I'm going the right path?
> Thanks to all in advance!
>
>|||Thanks to both! So I'll avoid cursors...
have a good day