Query A Table Twice in a DataSet

Question

I am trying to query a single table and return two sets of data into two columns for a report.

the table in question (tblReviewComments) looks something like this:

Key - ReviewCommentTypeID - PlainText
001 - 15 - "Text of Comment type 1"
002 - 10  - "Text of Commen type 2"

I am trying to return a distinct colum for each type of comment.

The complete query as is can be filtered to show any of the comment types.

SELECT
  tblReview.ReviewName
  ,tblReview.ReviewDate
  ,tblReviewBorrower.BorrowerName
  ,tblReviewBankInfo.BusinessDesc
  ,tblReviewNote.TotalCommitment
  ,tblReviewComment.ReviewCommentTypeId
  ,tblReviewComment.PlainText
FROM
  tblReviewBorrower
  INNER JOIN tblReviewBankInfo
    ON tblReviewBorrower.ReviewBorrowerId = tblReviewBankInfo.ReviewBorrowerId
  INNER JOIN tblReviewNote
    ON tblReviewBorrower.ReviewBorrowerId = tblReviewNote.ReviewBorrowerId
  INNER JOIN tblReview
    ON tblReviewBorrower.ReviewKey = tblReview.ReviewKey AND tblReviewNote.ReviewKey = tblReview.ReviewKey
  INNER JOIN tblReviewComment
    ON tblReviewBorrower.ReviewBorrowerId = tblReviewComment.ReviewBorrowerId AND tblReview.ReviewKey = tblReviewComment.ReviewKey

Answer 1

Hi Toby,

You can create two datasets with different where clause base on you requirement in the query, in this case you can get two different sets. And then you can use LookUp function to display data in different datasets. For more information about LookUp function, please see:
http://msdn.microsoft.com/en-us/library/ee210531.aspx
http://sqlcircuit.blogspot.com/2012/03/ssrs-2008-r2-lookup-how-to-use-multiple.html#!/2012/03/ssrs-2008-r2-lookup-how-to-use-multiple.html

If I have anything misunderstood, please point it out and elaborate what you wan to achieve so that we can make further analysis.

Regards,
Charlie Liao

If you have any feedback on our support, please click here.

---

Charlie Liao
TechNet Community Support

Answer 2

That did the trick. Thank you so much!