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
(30),
Name
Accomplishments
(30)
)
insert
values
'2007'
,
'Emp - 01'
'Sam'
'ALC Notes 1'
),
'ALC Notes 2'
'ALC Notes 3'
'Emp - 02'
'John'
''
'Emp - 03'
'Mary'
'Emp - 04'
'Akash'
SELECT
*
FROM
To group same row data with one column having varying data , I have used this T-SQL query :
,Emp_id,
STUFF((
' | '
+ Accomplishments
@sample_data X
WHERE
X.Emp_id = Y.Emp_id
GROUP
BY
,Accomplishments
FOR
XML PATH(
)), 1, 2,
) Accomplishments
@sample_data Y
To format the column with varying row data, in the report, I wrote an expression for Accomplishments column:
=Replace(Fields!Accomplishments.Value,
" |"
,VBCRLF)
Naomi N edited Revision 1. Comment: Article needs more work