SSRS Select Values From Rows as Fields

Question

I have a table that has two columns, ID and Value. For this example lets say there are three rows:

ID Value

A 1

B 2

C 3

The number of rows will remain constant, so I can create a field for each item I want to display, e.g. ItemAID, ItemBID, ItemCID, ItemAValue, ItemBValue, ItemCValue, however I don't know if that would be the best solution as it would require multiple select statements which SSRS doesn't seem to like.

What would be the best way of going about this, using multiple fields, writing a more elegant query or otherwise?

Answer 1

Found it.

Using:

 SELECT a.ID AS ItemAID, b.ID AS ItemBID FROM TestTable a, TestTable b WHERE a.ID='A' and b.ID = 'B'  

worked out to get me what I needed.

Answer 2

It sounds as though you are trying to pivot data - a Tablix (ie. a Matrix table) would be an SSRS-based solution to this issue.

Answer 3

Check out the the up-voted answer here. You can use FORM XML and PATH:

http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server/273330#273330