?
Solved

subquery result in one row

Posted on 2005-02-25
3
Medium Priority
?
344 Views
Last Modified: 2006-11-17
Lets say i have 2 tables 'book' and 'author'

If a book  has mulitiple authors I would like my query to return book and authors in one row like:

1 "bookA", "authorA, authorB"
2 "bookB", "authorA, authorC"
3 "bookC", "authorD"

My real query is more complex, but it is this concept that would help me out. Can it be done?

(I've also tried to return the subqueries result as XML and cast that to a string, but I don't think SQL server 2000 supports an XML returning query within an ordinary record returning query)

Any ideas?
0
Comment
Question by:pietjepuk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 23

Accepted Solution

by:
adathelad earned 1000 total points
ID: 13402611
I know this is just an example, but to provide a solution for you I need to adjust slightly your above tables:
i.e. if a book as multiple authors then you will need a 3rd table: BookAuthor to link a book to an author (create a many to many relationship)

First you can create this user defined function:

CREATE FUNCTION dbo.fxn_ConcatAuthors(@BookId INTEGER)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @Authors VARCHAR(1000)
SET @Authors = ''

SELECT @Authors = @Authors + a.AuthorName + ', '
FROM Author a
    JOIN BookAuthor ba ON a.AuthorId = ba.AuthorId
WHERE ba.BookId = @BookId

IF RIGHT(@Authors, 2) = ', '
    SET @Authors = LEFT(@Authors, LEN(@Authors) - 2)

RETURN @Authors
END
GO

Then, you can this this function like so:
SELECT BookId, BookName, dbo.fxn_ConcatAuthors(BookId)
FROM Book
ORDER BY BookId
0
 
LVL 1

Author Comment

by:pietjepuk
ID: 13402825
OK! Just what I need.

I've adapted the function to my situation:

CREATE FUNCTION dbo.ConcatFieldList(@DocumentId INTEGER, @fieldTemplateID INTEGER)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @FieldList VARCHAR(1000)
SET @FieldList = ''

SELECT @FieldList = @FieldList + dbo.veldString.waarde + ', '
FROM         dbo.veld INNER JOIN
                      dbo.veldInteger ON dbo.veld.ID = dbo.veldInteger.veldID INNER JOIN
                      dbo.veldString ON dbo.veldInteger.waarde = dbo.veldString.ID
WHERE     (dbo.veld.documentID = @DocumentId) AND (dbo.veld.veldTemplateID = @fieldTemplateID )

IF RIGHT(@FieldList, 2) = ', '
    SET @FieldList = LEFT(@FieldList, LEN(@FieldList) - 2)

RETURN @FieldList
END


Thanks!
0
 
LVL 23

Expert Comment

by:adathelad
ID: 13402852
No problem
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question