CODEWITHSUNDEEP
reportingservices-2005reporting-servicesssrs-grouping
Leonard
Leonard

Reputation: 3092

Consolidating multiple rows to a single row

Is it possible to join multiple row values to a single row? The stored procedure from where I acquire the data that I use return multiple, almost identical rows except that the category-column dfferentiates for products that have been assigned multiple categories. I would like to consolidate these categories to one column, separated by new lines. Example data:

Name        Article number   Sales    Sales Category
------------------------------------------------
Product 1   2059102-1        20520    Retailer 1
------------------------------------------------
Product 1   2059102-1        20520    Retailer 2
------------------------------------------------
Product 1   2059102-1        20520    Retailer 3
------------------------------------------------
Product 2   2059102-2        2050     Retailer 1
------------------------------------------------
Product 2   2059102-2        5302     Retailer 3

Desired result:

Name        Article number   Sales    Sales Category
------------------------------------------------
Product 1   2059102-1        20520    Retailer 1
                                      Retailer 2
                                      Retailer 3
------------------------------------------------
Product 2   2059102-2        2050     Retailer 1
                                      Retailer 3

Thank you!

Upvotes: 3

Views: 4639

Answers (1)

Scott Willeke
Scott Willeke

Reputation: 9335

Create an RDL table and setup a Grouping on the Detail section of the table with two grouping expressions (so you have a single Grouping in the Table Details, but with multiple grouping expressions). The grouping expressions should be one for each of the fields: Name, Article number.

Then put a column in the RDL table for each field (Name, Article number, Sales, Sales Category). The trick is putting a List control in the Sales Category cell. In the list add a textbox for the "Sales Category" field and I think you'll get the result you want.

There is also some nasty SQL tricks for "row concatenation", but that is not very maintainable IMHO.

Upvotes: 4

Related Questions