Share via

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

  • Article

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