Hi there, I'm trying to create a report with three columns of data, but I would like to turn Columns #2 and #3 into comma-delimited lists within cells grouped by Column #1. Basically here's what I'm trying to accomplish: (SQL Query returns this:) ---1----2----3--- ---A----J----M--- ---A----F----K--- ---A----P----R--- ---B----L----T--- (And I'd like to make the report look like this:) ----------------------------- ---A----J,F,P----M,K,R---- ---B------L---------T------ I'm not sure if this is something that should be done in SSRS or in the TSQL, so any advice would be appreciated. Thanks.

Hi, The best place to this is the t-sql as shown below --Sample Data declare @t table ( c1 char(1) ,c2 char(1) ,c3 char(1) ) insert into @t select 'A','J','M' union all select 'A','F','K' union all select 'A','P','R' union all select 'B','L','T' --Actual Query SELECT DISTINCT a.c1 , (STUFF((SELECT ',' + b.c2 FROM @t b WHERE a.c1 = b.c1 ORDER BY b.c2 FOR XML PATH(''), TYPE, ROOT).value('root[1]','nvarchar(max)'),1,1,'')) as c2 , (STUFF((SELECT ',' + b.c3 FROM @t b WHERE a.c1 = b.c1 ORDER BY b.c3 FOR XML PATH(''), TYPE, ROOT).value('root[1]','nvarchar(max)'),1,1,'')) as c3 FROM @t a ORDER BY a.c1 You can also do this in SSRS if you want. Kindly have a look below post to see how to do this.. http://beyondrelational.com/modules/2/blogs/115/posts/11154/string-concatenationaggregation-in-ssrs-column-or-row-group.aspx- Chintak (My Blog)

Hi, The best place to this is the t-sql as shown below --Sample Data declare @t table ( c1 char(1) ,c2 char(1) ,c3 char(1) ) insert into @t select 'A','J','M' union all select 'A','F','K' union all select 'A','P','R' union all select 'B','L','T' --Actual Query SELECT DISTINCT a.c1 , (STUFF((SELECT ',' + b.c2 FROM @t b WHERE a.c1 = b.c1 ORDER BY b.c2 FOR XML PATH(''), TYPE, ROOT).value('root[1]','nvarchar(max)'),1,1,'')) as c2 , (STUFF((SELECT ',' + b.c3 FROM @t b WHERE a.c1 = b.c1 ORDER BY b.c3 FOR XML PATH(''), TYPE, ROOT).value('root[1]','nvarchar(max)'),1,1,'')) as c3 FROM @t a ORDER BY a.c1 You can also do this in SSRS if you want. Kindly have a look below post to see how to do this.. http://beyondrelational.com/modules/2/blogs/115/posts/11154/string-concatenationaggregation-in-ssrs-column-or-row-group.aspx- Chintak (My Blog)

Thanks for the response, however I am receiving two errors with my syntax on the TSQL. Below is the actual query I have written up: SELECT DISTINCT a.County, (STUFF((SELECT ',' + b.Ori FROM dbo.Ori b WHERE a.County = b.County ORDER BY b.Ori FOR XML PATH(''), TYPE, ROOT).value('root[1]','nvarchar(max)'),1,1,'')) as Ori, (STUFF((SELECT ',' + b.Terminal_Id FROM dbo.Ori b WHERE a.Ori = b.Ori ORDER BY b.Terminal_Id FOR XML PATH(''), TYPE, ROOT).value('root[1]','nvarchar(max)'),1,1,'')) as Terminal_Id FROM dbo.Ori a ORDER BY a.County I am getting "Incorrect syntax" on Lines 5 and 8. All of the quotes in the code are single-quotes.

Hi JH You may get the desired output using below query; SELECT DISTINCT a.County, (STUFF((SELECT ',' + b.Ori FROM dbo.Ori b WHERE a.County = b.County ORDER BY b.Ori FOR XML PATH('')),1,1,'')) as Ori, (STUFF((SELECT ',' + b.Terminal_Id FROM dbo.Ori b WHERE a.Ori = b.Ori ORDER BY b.Terminal_Id FOR XML PATH('')),1,1,'')) as Terminal_Id FROM dbo.Ori a ORDER BY a.County Please let me know if this helps. Hopefully i have answered you correctly. Thanks, Hasham

I have attempted your answer as well, and I am still being thrown the same two syntax errors. Line 5 - "Incorrect syntax near the keyword 'FOR'." Line 8 - "Incorrect syntax near the keyword 'ORDER'." Thanks.

Hi JH ! Using the same example above i am not getting any error and get your desired output; Can you please provide us some sample data and your table schema; --Sample Data declare @t table ( County char(1) ,Ori char(1) ,Terminal_Id char(1) ) insert into @t select 'A','J','M' union all select 'A','F','K' union all select 'A','P','R' union all select 'B','L','T' -- SELECT * FROM @T --County Ori Terminal_Id --A J M --A F K --A P R --B L T --Actual Query SELECT DISTINCT a.County, (STUFF((SELECT ',' + b.Ori FROM @t b WHERE a.County = b.County ORDER BY b.Ori FOR XML PATH('')),1,1,'')) as Ori, (STUFF((SELECT ',' + b.Terminal_Id FROM @t b WHERE a.County = b.County ORDER BY b.Terminal_Id FOR XML PATH('')),1,1,'')) as Terminal_Id FROM @t a ORDER BY a.County --County Ori Terminal_Id --A F,J,P K,M,R --B L T Please let me know if this helps. Hopefully i have answered you correctly. Thanks, Hasham

I took your example that you put in the code block above, and I did a direct copy-and-paste into Management Studio, and it threw the same 2 errors. Line 27 - Incorrect syntax near the keyword 'FOR'. Line 30 - Incorrect syntax near the keyword 'ORDER'. The top half - with the sample data is working correctly. Here's the extent of the table schema I can give you: County = Varchar(2) - foreign key. Ori = Varchar(9) - primary key. Terminal_Id = Varchar(8) There are many other columns, but none of them are keys, and none are important to the query.

Hi Jh ! Please let me know the version of MS SQL Server using; SELECT @@VERSION Also, if you can get database compatibility level using Database properties -> Options Thank, Hasham Niaz

@@VERSION = SQL Server 2000 - 8.00.2282 (Intel X86) Compatibility Level = SQL Server 2000 (80). If this procedure is something I cannot accomplish with SQL 2000, then I will move to using the SSRS option for reaching my goal. I just wanted to try the SQL resolution first.

Sorry JH ! This code i have provided won't work in MS SQL 2000. I should have asked this lot earlier. I think you can use something like JOIN in SSRS for this. Please google it first. Thanks, Hasham Niaz

Hi There Please take a look on this blog post http://beyondrelational.com/modules/2/blogs/115/posts/11154/string-concatenationaggregation-in-ssrs-column-or-row-group.aspx Many thanks Syed Qazafi Anjum

Hi There Please take a look on this blog post http://beyondrelational.com/modules/2/blogs/115/posts/11154/string-concatenationaggregation-in-ssrs-column-or-row-group.aspx Many thanks Syed Qazafi Anjum

Hi There Please take a look on this blog post http://beyondrelational.com/modules/2/blogs/115/posts/11154/string-concatenationaggregation-in-ssrs-column-or-row-group.aspx Many thanks Syed Qazafi Anjum I have attempted the procedure explained in this blog post, but I get an error: "The Value expression for the text box "Ori" has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of the containing group, the name of the containing data region, or the name of a dataset." Here is my custom code: Dim ID As Integer = 0 Dim tempstr As String = "" Function AggregateString(County as String, List as String) If County = ID Then tempstr = tempstr + "," + List Else ID = County tempstr = List End If Return tempstr End Function And here are the expressions that are in those text boxes. Ori =RunningValue(Code.AggregateString(Fields!County.Value, Fields!Ori.Value),Max,"County") TerminalId =RunningValue(Code.AggregateString(Fields!County.Value, Fields!Terminal_Id.Value),Max,"County")

Hi, Have you added row group on Fields!County.Value ?- Chintak (My Blog)

Hmmm, I did miss this, so I believe I have this working now. Thank you all, and I have flagged both options as answers. Final result: *** If you are using SQL Server 2000 or earlier - use SSRS solution. *** If you are using newer than SQL Server 2000 - try the TSQL solution.