Agregating a column of text values

Question

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.

I have tried JOIN but not getting anywhere.

TIA, Kevin

Answer 1

Hi @Kevin M ,
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:

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.

Answer 2

Hi @Kevin M ,
You could update the output results you want in the original table, as shown in the following figure:
Design：

Preview：

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:

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.