Combining comments

simran · August 31, 2018, 12:01am

Hello

I am looking for some help on how to combine the comments from table based on column values

select * from comments
I have select query that looks into multiple table and gives me this result

id seq_ord seq ptr comments name order_num
1111 3 0 135402231432 ok to take 3 times Tylenol 500MG TAB 22
1111 2 0 135402231433 ok to take 2 times Tylenol 500MG TAB 22
1111 1 0 135402233121 ok to take 1 time Tylenol 500MG TAB 22
1111 1 2 135402233145 Repeat 2 weeks 1 time Tylenol 500MG TAB 22
2222 3 0 135402231467 ok to take 3 times Zantac 150 MG TAB 38
2222 2 0 135402231483 ok to take 2 times Zantac 150 MG TAB 38
2222 1 0 135402233195 ok to take 1 time Zantac 150 MG TAB 38
2222 1 2 135402233155 Repeat 2 weeks 1 time Zantac 150 MG TAB 38

Need the results like. Add the comments if the id, seq,order_num,name and order_num is same

id combined comments order_num
1111 seq 1:ok to take 1 time, seq 2:ok to take 2 times, seq 3: ok to take 3 times 22
1111 Repeat 2 weeks 1 time 22
2222 seq 1:ok to take 1 time, seq 2:ok to take 2 times, seq 3: ok to take 3 times 38
2222 Repeat 2 weeks 1 time 38

create table comments(
id INT NOT NULL,
seq_ord VARCHAR(10) NOT NULL,
seq VARCHAR(10) NOT NULL,
ptr VARCHAR(25) NOT NULL,
comments varchar(max),
name varchar(40),
order_num varchar(10)
);

INSERT INTO comments
VALUES ('1111','3','0','135402231432','ok to take 3 times','Tylenol 500MG TAB','22');
INSERT INTO comments
VALUES ('1111','2','0','135402231433','ok to take 2 times','Tylenol 500MG TAB','22');
INSERT INTO comments
VALUES ('1111','1','0','135402233121','ok to take 1 time','Tylenol 500MG TAB','22');
INSERT INTO comments
VALUES ('1111','1','2','135402233145','Repeat 2 weeks 1 time','Tylenol 500MG TAB','22');
INSERT INTO comments
VALUES ('2222','3','0','135402231467','ok to take 3 times','Zantac 150 MG TAB','38');
INSERT INTO comments
VALUES ('2222','2','0','135402231483','ok to take 2 times','Zantac 150 MG TAB','38');
INSERT INTO comments
VALUES ('2222','1','0','135402233195','ok to take 1 time','Zantac 150 MG TAB','38');
INSERT INTO comments
VALUES ('2222','1','2','135402233155','Repeat 2 weeks 1 time','Zantac 150 MG TAB','38');

Thanks
Asha

gigawatt38 · August 31, 2018, 1:11pm

Hi Asha,

Please try my solution:

/*sample DB table definition*/

create table dbo.comments(
 id INT NOT NULL,
 seq_ord VARCHAR(10) NOT NULL,
 seq VARCHAR(10) NOT NULL,
 ptr VARCHAR(25) NOT NULL,
 comments varchar(max),
 name varchar(40),
 order_num varchar(10)
 );

INSERT INTO dbo.comments
 VALUES ('1111','3','0','135402231432','ok to take 3 times','Tylenol 500MG TAB','22');
 INSERT INTO dbo.comments
 VALUES ('1111','2','0','135402231433','ok to take 2 times','Tylenol 500MG TAB','22');
 INSERT INTO dbo.comments
 VALUES ('1111','1','0','135402233121','ok to take 1 time','Tylenol 500MG TAB','22');
 INSERT INTO dbo.comments
 VALUES ('1111','1','2','135402233145','Repeat 2 weeks 1 time','Tylenol 500MG TAB','22');
 INSERT INTO dbo.comments
 VALUES ('2222','3','0','135402231467','ok to take 3 times','Zantac 150 MG TAB','38');
 INSERT INTO dbo.comments
 VALUES ('2222','2','0','135402231483','ok to take 2 times','Zantac 150 MG TAB','38');
 INSERT INTO dbo.comments
 VALUES ('2222','1','0','135402233195','ok to take 1 time','Zantac 150 MG TAB','38');
 INSERT INTO dbo.comments
 VALUES ('2222','1','2','135402233155','Repeat 2 weeks 1 time','Zantac 150 MG TAB','38');






select * from dbo.comments;

-----------------------------------------

/*final select*/
SELECT
--
	qq.id,
--
	STUFF(
		(
			SELECT DISTINCT ', '
				+ 'seq '
				+ CAST(seq_ord AS varchar)
				+ ': '
				+ comments
          		FROM dbo.comments
			WHERE qq.id = id
				AND qq.seq = seq
				AND qq.order_num = order_num
				AND qq.name = name
			FOR XML PATH ('')
		), 1, 1, ''
	)  AS combined_comments,
--
	qq.order_num
--
FROM dbo.comments qq
GROUP BY qq.id, qq.seq, qq.order_num, qq.name
ORDER BY 1, 2;

Regards,
gigawatt38
MCSA: SQL 2016 Database Development
MCSE: Data Management and Analytics

Tested by: http://rextester.com/l/sql_server_online_compiler

simran · August 31, 2018, 4:42pm

Please disregard my question as I played with this and see why it was used

harishgg1 · September 1, 2018, 9:35am

hi i have this idea

select ID,seq_ord,seq,ptr,name,order_num,
case when ( id, seq,order_num,name and order_num is same) then comments else "" end as comments
from dbo.comments

harishgg1 · September 2, 2018, 8:12am

in the below link there are variety of ways of doing this ....

simran · September 2, 2018, 3:39pm

Thanks so much Harish Ji for your expert help. This will be really helpful as I am finding that not all orders have comments so will look for it to work on this.

Best Regards

harishgg1 · September 2, 2018, 3:44pm

If you and me can chat at the same time while both of us are working on it
It will be very fast..

simran · September 5, 2018, 11:37pm

Hello Gigawatt38 and Harrish thanks for your kind elp

Gigawatt, I am trying to change it so the seq num does not print in case there is no comments.
I tried following, can you please suggest

/****** Script for SelectTopNRows command from SSMS ******/

create table [StagingDB].[dbo].[comments](
id INT NOT NULL,
seq_ord VARCHAR(10) NOT NULL,
seq VARCHAR(10) NOT NULL,
ptr VARCHAR(25) NOT NULL,
comments varchar(max),
name varchar(40),
order_num varchar(10)
);

INSERT INTO [StagingDB].[dbo].[comments]
VALUES ('1111','3','0','135402231432','ok to take 3 times','Tylenol 500MG TAB','22');
INSERT INTO [StagingDB].[dbo].[comments]
VALUES ('1111','2','0','135402231433','ok to take 2 times','Tylenol 500MG TAB','22');
INSERT INTO [StagingDB].[dbo].[comments]
VALUES ('1111','1','0','135402233121','ok to take 1 time','Tylenol 500MG TAB','22');
INSERT INTO [StagingDB].[dbo].[comments]
VALUES ('1111','1','2','135402233145','Repeat 2 weeks 1 time','Tylenol 500MG TAB','22');
INSERT INTO [StagingDB].[dbo].[comments]
VALUES ('2222','3','0','135402231467','ok to take 3 times','Zantac 150 MG TAB','38');
INSERT INTO [StagingDB].[dbo].[comments]
VALUES ('2222','2','0','135402231483','ok to take 2 times','Zantac 150 MG TAB','38');
INSERT INTO [StagingDB].[dbo].[comments]
VALUES ('2222','1','0','135402233195','ok to take 1 time','Zantac 150 MG TAB','38');
INSERT INTO [StagingDB].[dbo].[comments]
VALUES ('2222','1','2','135402233155','Repeat 2 weeks 1 time','Zantac 150 MG TAB','38');
INSERT INTO [StagingDB].[dbo].[comments]
VALUES ('3333','3','0','135402231407','','Aspirin 100 MG TAB','48');
INSERT INTO [StagingDB].[dbo].[comments]
VALUES ('3333','2','0','135402231408','','Aspirin 100 MG TAB','48');
INSERT INTO [StagingDB].[dbo].[comments]
VALUES ('3333','1','0','135402233109','ok to take 1 time','Aspirin 100 MG TAB','48');
INSERT INTO [StagingDB].[dbo].[comments]
VALUES ('3333','1','2','135402233111','','Aspirin 100 MG TAB','48');

/final select/
SELECT

qq.id,
qq.name,
qq.ptr,

--
STUFF(
(
SELECT DISTINCT ', '
+ 'seq '
+ CAST(seq_ord AS varchar)
+ ': '
+ comments
FROM [StagingDB].[dbo].[comments]
WHERE qq.id = id
AND qq.seq = seq
AND qq.order_num = order_num
AND qq.name = name
AND qq.comments!=''
FOR XML PATH ('')
), 1, 1, ''
) AS combined_comments,

qq.order_num

--
FROM [StagingDB].[dbo].[comments] qq
GROUP BY qq.id, qq.name,qq.ptr,qq.seq, qq.order_num, qq.name,qq.comments
ORDER BY 1, 2;

id name ptr combined_comments order_num
1111 Tylenol 500MG TAB 135402231432 seq 1: ok to take 1 time, seq 2: ok to take 2 times, seq 3: ok to take 3 times 22
1111 Tylenol 500MG TAB 135402231433 seq 1: ok to take 1 time, seq 2: ok to take 2 times, seq 3: ok to take 3 times 22
1111 Tylenol 500MG TAB 135402233121 seq 1: ok to take 1 time, seq 2: ok to take 2 times, seq 3: ok to take 3 times 22
1111 Tylenol 500MG TAB 135402233145 seq 1: Repeat 2 weeks 1 time 22
2222 Zantac 150 MG TAB 135402231467 seq 1: ok to take 1 time, seq 2: ok to take 2 times, seq 3: ok to take 3 times 38
2222 Zantac 150 MG TAB 135402231483 seq 1: ok to take 1 time, seq 2: ok to take 2 times, seq 3: ok to take 3 times 38
2222 Zantac 150 MG TAB 135402233155 seq 1: Repeat 2 weeks 1 time 38
2222 Zantac 150 MG TAB 135402233195 seq 1: ok to take 1 time, seq 2: ok to take 2 times, seq 3: ok to take 3 times 38
3333 Aspirin 100 MG TAB 135402231407 NULL 48
3333 Aspirin 100 MG TAB 135402231408 NULL 48
3333 Aspirin 100 MG TAB 135402233109 seq 1: ok to take 1 time, seq 2: , seq 3: 48
3333 Aspirin 100 MG TAB 135402233111 NULL 48

I need to see

id name ptr combined_comments order_num
1111 Tylenol 500MG TAB 135402231432 seq 1: ok to take 1 time, seq 2: ok to take 2 times, seq 3: ok to take 3 times 22
1111 Tylenol 500MG TAB 135402231433 seq 1: ok to take 1 time, seq 2: ok to take 2 times, seq 3: ok to take 3 times 22
1111 Tylenol 500MG TAB 135402233121 seq 1: ok to take 1 time, seq 2: ok to take 2 times, seq 3: ok to take 3 times 22
1111 Tylenol 500MG TAB 135402233145 seq 1: Repeat 2 weeks 1 time 22
2222 Zantac 150 MG TAB 135402231467 seq 1: ok to take 1 time, seq 2: ok to take 2 times, seq 3: ok to take 3 times 38
2222 Zantac 150 MG TAB 135402231483 seq 1: ok to take 1 time, seq 2: ok to take 2 times, seq 3: ok to take 3 times 38
2222 Zantac 150 MG TAB 135402233155 seq 1: Repeat 2 weeks 1 time 38
2222 Zantac 150 MG TAB 135402233195 seq 1: ok to take 1 time, seq 2: ok to take 2 times, seq 3: ok to take 3 times 38
3333 Aspirin 100 MG TAB 135402231407 NULL 48
3333 Aspirin 100 MG TAB 135402231408 NULL 48
3333 Aspirin 100 MG TAB 135402233109 seq 1: ok to take 1 time 48
3333 Aspirin 100 MG TAB 135402233111 NULL 48

harishgg1 · September 6, 2018, 7:23am

Hi Aasha

I tried to do it

Is this what you are looking for

Create Data Script

DROP TABLE [harcomments] 

go 

CREATE TABLE [harcomments] 
  ( 
     id        INT NOT NULL, 
     seq_ord   VARCHAR(10) NOT NULL, 
     seq       VARCHAR(10) NOT NULL, 
     ptr       VARCHAR(25) NOT NULL, 
     comments  VARCHAR(max), 
     NAME      VARCHAR(40), 
     order_num VARCHAR(10) 
  ); 

go 

INSERT INTO [harcomments] 
VALUES      ('1111', 
             '3', 
             '0', 
             '135402231432', 
             'ok to take 3 times', 
             'Tylenol 500MG TAB', 
             '22'); 

INSERT INTO [harcomments] 
VALUES      ('1111', 
             '2', 
             '0', 
             '135402231433', 
             'ok to take 2 times', 
             'Tylenol 500MG TAB', 
             '22'); 

INSERT INTO [harcomments] 
VALUES      ('1111', 
             '1', 
             '0', 
             '135402233121', 
             'ok to take 1 time', 
             'Tylenol 500MG TAB', 
             '22'); 

INSERT INTO [harcomments] 
VALUES      ('1111', 
             '1', 
             '2', 
             '135402233145', 
             'Repeat 2 weeks 1 time', 
             'Tylenol 500MG TAB', 
             '22'); 

INSERT INTO [harcomments] 
VALUES      ('2222', 
             '3', 
             '0', 
             '135402231467', 
             'ok to take 3 times', 
             'Zantac 150 MG TAB', 
             '38'); 

INSERT INTO [harcomments] 
VALUES      ('2222', 
             '2', 
             '0', 
             '135402231483', 
             'ok to take 2 times', 
             'Zantac 150 MG TAB', 
             '38'); 

INSERT INTO [harcomments] 
VALUES      ('2222', 
             '1', 
             '0', 
             '135402233195', 
             'ok to take 1 time', 
             'Zantac 150 MG TAB', 
             '38'); 

INSERT INTO [harcomments] 
VALUES      ('2222', 
             '1', 
             '2', 
             '135402233155', 
             'Repeat 2 weeks 1 time', 
             'Zantac 150 MG TAB', 
             '38'); 

INSERT INTO [harcomments] 
VALUES      ('3333', 
             '3', 
             '0', 
             '135402231407', 
             NULL, 
             'Aspirin 100 MG TAB', 
             '48'); 

INSERT INTO [harcomments] 
VALUES      ('3333', 
             '2', 
             '0', 
             '135402231408', 
             NULL, 
             'Aspirin 100 MG TAB', 
             '48'); 

INSERT INTO [harcomments] 
VALUES      ('3333', 
             '1', 
             '0', 
             '135402233109', 
             'ok to take 1 time', 
             'Aspirin 100 MG TAB', 
             '48'); 

INSERT INTO [harcomments] 
VALUES      ('3333', 
             '1', 
             '2', 
             '135402233111', 
             NULL, 
             'Aspirin 100 MG TAB', 
             '48'); 

go

My SQL

SELECT a.id, 
       a.NAME, 
       a.ptr, 
       b.combined_comments, 
       a.order_num 
FROM   harcomments a 
       JOIN (SELECT id, 
                    seq, 
                    Combined_Comments = Stuff ((SELECT ',SEQ ' + InrTab.comments 
                                                FROM   harcomments InrTab 
                                                WHERE  InrTab.id = OutTab.id 
                                                       AND InrTab.seq = 
                                                           OutTab.seq 
                                                ORDER  BY InrTab.id 
                                                FOR xml path('')), 1, 1, Space(0 
                                        )) 
             FROM   harcomments OutTab 
             GROUP  BY OutTab.id, 
                       OutTab.seq) b 
         ON a.id = b.id 
            AND a.seq = b.seq 
ORDER  BY a.id, 
          a.seq

My Output

simran · September 6, 2018, 4:46pm

Thanks so much Harrish for helping. I need little different output like we were getting from Gigawatt38 solution like including the seq 1: but I need to incorporate it so the seq # only shows if there is comment in that seq_ord

harishgg1 · September 7, 2018, 5:44am

Hi Aasha

Here is my SQL Script

My SQL

SELECT a.id, 
       a.NAME, 
       a.ptr, 
       b.combined_comments, 
       a.order_num 
FROM   (SELECT * 
        FROM   harcomments) a 
       JOIN (SELECT qq.id, 
                    qq.seq, 
                    Stuff((SELECT DISTINCT ', ' + 'seq ' + Cast(seq_ord AS 
                                           VARCHAR) + 
                                           ': ' 
                                           + comments 
                           FROM   harcomments 
                           WHERE  qq.id = id 
                                  AND qq.seq = seq 
                                  AND qq.order_num = order_num 
                                  AND qq.NAME = NAME 
                           FOR xml path ('')), 1, 1, '') AS combined_comments, 
                    qq.order_num 
             FROM   harcomments qq 
             GROUP  BY qq.id, 
                       qq.seq, 
                       qq.order_num, 
                       qq.NAME) b 
         ON a.id = b.id 
            AND a.seq = b.seq

Here is the output
Please check and let me know

OUTPUT

simran · September 17, 2018, 5:49pm

Thanks so much Harrish for your consistent help and follow-up through understanding and resolving the issue. Appreciate the time and willingness to share your knowledge. I am able to use the solution to combine comments from different records and account for empty strings

Thanks so much all for helping the new bee

Combining comments

/final select/ SELECT

-- STUFF( ( SELECT DISTINCT ', ' + 'seq ' + CAST(seq_ord AS varchar) + ': ' + comments FROM [StagingDB].[dbo].[comments] WHERE qq.id = id AND qq.seq = seq AND qq.order_num = order_num AND qq.name = name AND qq.comments!='' FOR XML PATH ('') ), 1, 1, '' ) AS combined_comments,

/final select/
SELECT

--
STUFF(
(
SELECT DISTINCT ', '
+ 'seq '
+ CAST(seq_ord AS varchar)
+ ': '
+ comments
FROM [StagingDB].[dbo].[comments]
WHERE qq.id = id
AND qq.seq = seq
AND qq.order_num = order_num
AND qq.name = name
AND qq.comments!=''
FOR XML PATH ('')
), 1, 1, ''
) AS combined_comments,