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

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:

 Mom1      
      children 1
      children 2
Mom2      
      children 1
      children 2
      children 3
      children 4
      children 5
Mom3      
      children 1
      children 2
      children 3

I WANT: two columns display the values like this

Mom1      
      children1, children2
Mom2      
      children1, children2, children3, children4, children 5
Mom3      
      children1, children2, children3

It's better related to SSRS report design, any thoughts? urgent, many thanks
jc50967wAsked:
Who is Participating?
 
JestersGrindConnect With a Mentor Commented:
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.

SELECT
   t1.Parent,
   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

Greg

0
 
mlmccCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.