May 16, 2007 at 1:13 pm
Hello All,
Does anyone know a better way to do this?
I have a query where there is a one-to-many relationship. For a generic example, one author to many books.
I want to write a query where I can have the result like below
Author Books
------- --------
John Smith Some Book, Some Other Book, YA Book
Jill Jones Great Book, Average Book, Bad Book
where as a normal query with normal joins would return
Author Book
------- ---------
John Smith Some Book
John Smith Some Other Book
John Smith YA Book
Jill Jones Great Book
Jill Jones Average Book
Jill Jones Bad Book
Does that make sense?
I can use a cursor to take an author id, then query the book table, concatenating the names, then SELECT that out for the column.
If at all possible, for efficiency's sake, I would like to use a set-based solution if one is available and ditch the cursor.
Do any of you have any suggestions?
Thank you very much.
May 16, 2007 at 8:05 pm
I found the answer. What you want to do is something like this:
DECLARE @result varchar(max)
SET @result = ''
SELECT @result = @result + book_name + ', '
FROM book
WHERE author_id = 'some_author_id'
SELECT @result
I actually put that code into a function, passed in the id, and returned the string. Also before I returned it, I chopped off the last comma. I then just selected from the function right in my original statement.
SELECT author_name, fn_my_function(author_id)
FROM author
Worked like a charm.
I am also aware that there are several ways to kill the last comma in the select statement itself (an ISNULL and preceding instead of trailing commas).
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply