Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

subquery result in one row

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
pietjepuk
Asked:
pietjepuk
  • 2
1 Solution
 
adatheladCommented:
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
 
pietjepukAuthor Commented:
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
 
adatheladCommented:
No problem
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now