Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

String aggregation in SQL Server

I have a table as below :

COMMENT_ID ID USER_COMMENTS
1521013*1521167-1 1521013*1521167 This is to inform
1521013*1521167-3 1521013*1521167 asking for
1521013*1521167-4 1521013*1521167 detailed report.
1521013*1521167-2 1521013*1521167 that user is

Need to aggregate the user_comments based on comment_id

Desired OP:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

ID COMMENT
1521013*1521167 This is to inform that user is asking for detailed report.

I tried below :

 SELECT ID,STRING_AGG(USER_COMMENTS,' ') comment from table_A group by ID;

it gives me :

ID comment
1521013*1521167 This is to inform asking for detailed report. that user is

The comments are not in order by comment_id

DDLs are as below:

CREATE TABLE TABLE_A
(
  COMMENT_ID VARCHAR(100),
  ID VARCHAR(100),
  USER_COMMENTS VARCHAR(100)
  );
  
  INSERT INTO TABLE_A VALUES ('1521013*1521167-1','1521013*1521167','This is to inform');
  INSERT INTO TABLE_A VALUES ('1521013*1521167-3','1521013*1521167','asking for ');
  INSERT INTO TABLE_A VALUES ('1521013*1521167-4','1521013*1521167','detailed report.');
  INSERT INTO TABLE_A VALUES ('1521013*1521167-2','1521013*1521167','that user is ');

How do I aggregate it based on comment_id order ?

Any inputs are appreciated.

Thanks

>Solution :

Please try

SELECT ID,STRING_AGG(USER_COMMENTS,' ')WITHIN GROUP (ORDER BY COMMENT_ID ASC) AS comment 
FROM table_A 
GROUP BY ID
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading