SSRS: How to Group Same Row Data with One Column Having Varying Data

SSRS: How to Group Same Row Data with One Column Having Varying Data

This article is the outcome of my  answer to this question in the SSRS forum.

Consider this sample data

DECLARE  @sample_data table
(
[Year] varchar(5),
Emp_id varchar(30),
Name varchar(30),
Accomplishments varchar(30)
)
insert @sample_data values
('2007','Emp - 01','Sam','ALC Notes 1'),
('2007','Emp - 01','Sam','ALC Notes 2'),
('2007','Emp - 01','Sam','ALC Notes 3'),
('2007','Emp - 02','John',''),
('2007','Emp - 03','Mary',''),
('2007','Emp - 04','Akash','')
SELECT * FROM @sample_data

To group same row data with one column having varying data ,
I have used this T-SQL query :

SELECT Year,Emp_id,Name
       STUFF((SELECT ' | ' + Accomplishments
              FROM   @sample_data X WHERE X.Emp_id = Y.Emp_id GROUP  BY Year,Emp_id,Name,Accomplishments
              FOR XML PATH('')), 1, 2, '') Accomplishments                
FROM   @sample_data Y
GROUP  BY Year,Emp_id,Name



To format the column with varying row data, in the report, I wrote an expression for Accomplishments column:

=Replace(Fields!Accomplishments.Value," |",VBCRLF)



Leave a Comment
  • Please add 4 and 2 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Naomi  N edited Revision 1. Comment: Article needs more work

Page 1 of 1 (1 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Naomi  N edited Revision 1. Comment: Article needs more work

Page 1 of 1 (1 items)