Hi, I am new to sql server reporting. I have a dataset, which returns data like listed below: name1 name2 name3 name4 t1 t2 t3 t4 t1 t2 t3 t5 a1 a2 a3 a4 the reporting should be grouped by (name1+name2+name3) for instance : result should like t1 t2 t3 t4,t5 a1 a2 a3 a4 anyone knows how should I achieve this using sql server reporting 2008. I can use expression to group name1+name2+name3 but no idea how to combine the field name4 into a comma seperated value. Please help. Thanks in advanced!

You can get this done in your sql query. insert into @temp_tbl (name1,name2,name3, name4) select 't1' as name1, 't2' as name2,'t3' as name3,'t4' as name4 union all select 't1','t2','t3','t5' union all select 'a1','a2','a3','a4' select groups.name1, groups.name2, groups.name3, B.allName4 from ( select name1, name2, name3 from @temp_tbl group by name1, name2, name3 ) groups cross apply ( select replace( (select name4 as [data()] from @temp_tbl where name1 = groups.name1 and name2 = groups.name2 and name3 = groups.name3 for xml path('') ), ' ', ',') )B(allName4)

CREATE TABLE #TEMP ( name1 VARCHAR(10), name2 VARCHAR(10), name3 VARCHAR(10), name4 VARCHAR(10) ) INSERT INTO #TEMP VALUES ('t1', 't2', 't3', 't4') INSERT INTO #TEMP VALUES ('t1', 't2', 't3', 't5') INSERT INTO #TEMP VALUES ('a1', 'a2', 'a3', 'a4') ;WITH cte AS ( SELECT name1+' '+name2+' '+name3 AS name123, name4 FROM #TEMP ) SELECT name123, STUFF((SELECT ','+name4 FROM cte B WHERE A.name123 = B.name123 FOR XML PATH('') ),1,1,'') AS name4 FROM cte A GROUP BY name123 DROP TABLE #TEMP KG, MCTS