CODEWITHSUNDEEP
reporting-services
amarcy
amarcy

Reputation: 1515

SSRS Select Values From Rows as Fields

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?

Upvotes: 0

Views: 838

Answers (3)

D.S.
D.S.

Reputation: 1413

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

How to use GROUP BY to concatenate strings in SQL Server?

Upvotes: 1

user359040
user359040

Reputation:

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.

Upvotes: 0

amarcy
amarcy

Reputation: 1515

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.

Upvotes: 0

Related Questions