question

KevinM-207 avatar image
0 Votes"
KevinM-207 asked Joyzhao-MSFT commented

Agregating a column of text values

Hi!

I'm working in MS Report Builder and trying to create a summary of text values from a column by building them into one string.

In the table example below, I have 3 rows of data with 3 different text values of colors. I want to take all 3 values and put them into row 4 as one string with commas separating them as shown in the example.

76395-table.png

I have tried JOIN but not getting anywhere.

TIA, Kevin


sql-server-reporting-services
table.png (3.0 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @KevinM-207 ,
Has your issue been resolved?
If you have any question,please feel free to let me know.
If the answer is helpful, please click "Accept Answer" and upvote it.

0 Votes 0 ·
Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered Joyzhao-MSFT commented

Hi @KevinM-207 ,
Using TSQL statements will be relatively simple.
You can refer to the following two methods:
Method 1:

   SELECT Color = STUFF((
          SELECT ',' + Color
             FROM TableName
             FOR XML PATH('')
          ), 1, 1, '')

Method 2:

 SELECT STRING_AGG( ISNULL(Color, ' '), ',') As Color
    FROM TableName

Output:
76507-02.jpg

Hope this helps.
Best Regards,
Joy


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.




02.jpg (6.7 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi!

I think part of my confusion is where the SQL statement goes. I am just learning SSRS but have been writing reports for 20+ years using Crystal Reports. I tried adding the statement to the group row as an expression and it isn't working.

Thanks, Kevin

0 Votes 0 ·

At present I have not found a suitable solution to use expressions to meet your needs, I will continue to follow up this case. But I have to say that some of the effects that can be achieved by Tsql statements are not well represented in SSRS.

0 Votes 0 ·
Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered Joyzhao-MSFT edited

Hi @KevinM-207 ,
You could update the output results you want in the original table, as shown in the following figure:
Design:
76648-03.jpg
Preview:
76649-04.jpg
Refer to the following TSQL statement to update the original table:

 IF exists (select * from sysobjects where name='TableName')
    drop table TableName

 CREATE TABLE TableName(
        RowNumber     Int IDENTITY(1,1) NOT NULL  
       ,Color VARCHAR(50) NOT NULL
     );
    
  INSERT INTO TableName(Color) 
     VALUES ('Red'),
       ('Yellow'),
       ('Blue');
       
   INSERT INTO TableName
      SELECT Color = STUFF((
           SELECT ',' + Color
              FROM TableName
              FOR XML PATH('')
           ), 1, 1, '')
    
   SELECT * FROM TableName

Output:
76539-05.jpg
Best Regards,
Joy


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


03.jpg (5.3 KiB)
04.jpg (11.7 KiB)
05.jpg (10.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.