Consolidating multiple rows to a single row

Question

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!

Does it has to be a line break in Sales Category? – niktrs Sep 02 '11 at 12:23 — niktrs, Sep 02 '11 at 12:23
Yes, unfortunately that's what our client has specified. – Leonard Sep 02 '11 at 14:38 — Leonard, Sep 02 '11 at 14:38

Scott Willeke · Accepted Answer · 2014-08-01T01:37:48.853

4

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.

edited Aug 01 '14 at 01:37

answered Sep 03 '11 at 04:30

Scott Willeke

8,884
1
40
52

I think that export in excel doesn't work in this case (Data Regions within table/matrix cells are ignored.) – niktrs Sep 05 '11 at 08:42
2

Been searching for this solution for 2 days. Thanks for solving it in 2011! (upvote) – BrettFromLA Jul 30 '14 at 21:34

Consolidating multiple rows to a single row

1 Answers1