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)





See Also