I have a complex query where each row in the final dataset is a product.
However each product has a number of authors associated with it. What I
would like to do is have a query/subroutine join the authors to the product,
as a string:
ProductID Title Authors
1 The Sacred and the Profane John Rieggle, George Alexi
2 Dancingin the Dark DanBrown, Peter Kay, Paul
Dwebinski
Products Table
==============
ProductID
Title
Authors Table
=============
AuthorID
Name
Product Authors Table
=====================
AuthorID
ProductID
Is this at all possible?
Thanks
jr.
It is possible with the use of a UDF. However, I highly suggestthat you do this work on the front end. Preparing the data foroutput is not something the database engine should be doing, and theUDF method is fairly intensive as it results in additional queries.
Here's a link showing the UDF method:Joining Strings into Delimiter Separated Lists.
.
|||Terri,
Thanks for your reply. I understand that this is a UI rendering issue, and I have been toying with how best to proceed. Currently my paging routine returns one resultset, were each row represents a product. To return the authors, I would need to return a second resultset, and join them at the middle tier:
Pseudo steps:
1. Do complex paging query
2. Based on items in paging query, join them to the authors table and return that resultset too.
This would solve the problem, but sends more data across the wire which I was thinknig could be avoided, even though I know that the DB shouldnt be doing ui rendering steps. Other than "best practice" I see no other reason to put this code in the ui, since it is just a dumb string without much meaning when rendered in a listing. As you mention though, this maybe putting too much stress on the DB when using a UDF...I am relatively new to this so forgive my naivete...
Thanks Terri,
jr.|||
This might not be very efficient if you have a large sum of data, but here is how to do it.
create table #product (
productID int,
productTitle varchar(50)
)
go
create table #author (
authorID int,
name varchar(100)
)
go
create table #productAuthor (
productID int,
authorID int
)
go
insert into #product values (1,'product1')
insert into #product values (2,'product2')
insert into #author values (1,'author1')
insert into #author values (2,'author2')
insert into #author values (3,'author3')
insert into #author values (4,'author4')
insert into #productAuthor values (1,1)
insert into #productAuthor values (1,2)
insert into #productAuthor values (2,3)
insert into #productAuthor values (2,4)
create table #nameList (
productid int,
names varchar(1000)
)
go
select distinct p.productid into #tmp
from #product p
inner join #productAuthor pa on p.productid = pa.productid
declare @.productid int,
@.names varchar(1000)
while exists (select null from #tmp)
begin
set @.names = ''
select top 1 @.productid = productid from #tmp
select @.names = CASE WHEN @.names = '' THEN name ELSE @.names + ',' + name END
from #product p
inner join #productAuthor pa on p.productid = pa.productid
inner join #author a on pa.authorid = a.authorid
where p.productid = @.productid
insert into #nameList values (@.productid, @.names)
delete from #tmp where productid = @.productid
end
select p.productID, productTitle, nl.names
from #product p
inner join #nameList nl ON p.productid = nl.productID
Let me know if you have any questions.
Nick
)