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:
| 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