[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


how to roll values from a field into one row in SSRS

Posted on 2011-10-27
Medium Priority
Last Modified: 2012-05-12
Hi , this is a kind of concatenation request. I have two fields Mom and Children in the dataset, Field 1 Mom is top level of Field 2 Children in this SSRS report,  I need to roll all F2 values from many rows into one line, instead to display each row of F2 under each F1.

I DON'T want:

      children 1
      children 2
      children 1
      children 2
      children 3
      children 4
      children 5
      children 1
      children 2
      children 3

I WANT: two columns display the values like this

      children1, children2
      children1, children2, children3, children4, children 5
      children1, children2, children3

It's better related to SSRS report design, any thoughts? urgent, many thanks
Question by:jc50967w
LVL 21

Accepted Solution

JestersGrind earned 2000 total points
ID: 37038675
I would do it in the dataset instead of in the report.  Something like the following will give you a comma delimited set of data.

   Children = substring(
      (SELECT ( ', ' + t2.Child )
      FROM YourTable t2
      WHERE t1.Parent = t2.Parent
      ORDER BY t2.Parent, t2.Child
      FOR XML PATH( '' )
                  ), 3, 1000 )
FROM YourTable t1
GROUP BY t1.Parent


LVL 101

Expert Comment

ID: 37181747
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Written by Valentino Vranken. Introduction: In a previous article (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Reporting-On-Data-From-Stored-Procedures-part-1.html) I announced that I would writ…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Loops Section Overview
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question