Solved

Concatenating multiple comments into one row

Posted on 2016-11-18
16
34 Views
Last Modified: 2016-12-01
I need to concatenate comments based on the customer ID, activity date, activity code, activity time.

I wrote code to get the duplicate number which tells you how many rows one comment should consist of.

 I need assistance how to concatenate the rows into one where the dup number is greater than 1.

I have attached a picture and highlighted what I am looking for.
0
Comment
Question by:Shalver
  • 5
  • 4
  • 3
  • +2
16 Comments
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
sample code please, attachment missing?
0
 

Author Comment

by:Shalver
Comment Utility
0
 

Author Comment

by:Shalver
Comment Utility
I think I have attached the file ..
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Not really sure what you mean by 'contatenate rows', please give us a data mockup based on your attachement.

Also give the article T-SQL:  Normalized data to a single comma delineated string and back a read and let us know if this is what you want...
0
 

Author Comment

by:Shalver
Comment Utility
Here is what The results should look like
Cus	_dupe_num	ActivityDate	ActivityCode	Collector	ActivityTime	ActivityNotes
4293312	1	11/17/2016	COMM	TW1 	16261	TCPA GIVEN ON 11/04/16                            
4293312	1	11/17/2016	PBCC	TW1 	16252	APP - QA-VI/ALL MM ADVSD 27 DPD $312.20 TAD APP SD +  CO/APP WLL BE MKNG PYMNT ON PAY DAY AND THINKS HE  + GETS PAID TMRRW, I ADVSD TO CLL CO/APP WHN HE TKES + A BREAK AND HVE HIM CLL BEFORE TMRRW AND HE SD CO/ + APP WLL CLL WHN HE GETS HIS CHECK AND CASHES IT, I + ADVSD ANTHR DUE DATE AND CAN NOT CNTNUE TO CARRY  + LOAN THIS WAY, HE STARTED USING FOUL LANGUAGE AND  + SD WE'LL GET OUR MONEY AND HUNG UP                
4293312	1	11/17/2016	PCNA	TW1 	16091	APP - NO ANSWER                                   
4293312	1	11/17/2016	PCNA	TW1 	16120	CO/APP - NO ANSWER                                
4293312	1	11/17/2016	PHLM	TW1 	16132	CO/APP - FEMALE SD APP WNT TO WRK WLL GVE MSG     

Open in new window

Currently it looks like this
Cus	_dupe_num	ActivityDate	ActivityCode	Collector	ActivityTime	ActivityNotes	SeqNo
4293312	1	11/17/2016	COMM	TW1 	16261	TCPA GIVEN ON 11/04/16                            	0000079663D
4293312	1	11/17/2016	PBCC	TW1 	16252	APP - QA-VI/ALL MM ADVSD 27 DPD $312.20 TAD APP SD	0000079663F
4293312	2	11/17/2016	PBCC	TW1 	16252	CO/APP WLL BE MKNG PYMNT ON PAY DAY AND THINKS HE 	0000079663G
4293312	3	11/17/2016	PBCC	TW1 	16252	GETS PAID TMRRW, I ADVSD TO CLL CO/APP WHN HE TKES	0000079663H
4293312	4	11/17/2016	PBCC	TW1 	16252	A BREAK AND HVE HIM CLL BEFORE TMRRW AND HE SD CO/	0000079663I
4293312	5	11/17/2016	PBCC	TW1 	16252	APP WLL CLL WHN HE GETS HIS CHECK AND CASHES IT, I	0000079664{
4293312	6	11/17/2016	PBCC	TW1 	16252	ADVSD ANTHR DUE DATE AND CAN NOT CNTNUE TO CARRY  	0000079664A
4293312	7	11/17/2016	PBCC	TW1 	16252	LOAN THIS WAY, HE STARTED USING FOUL LANGUAGE AND 	0000079664B
4293312	8	11/17/2016	PBCC	TW1 	16252	SD WE'LL GET OUR MONEY AND HUNG UP                	0000079664C
4293312	1	11/17/2016	PCNA	TW1 	16091	APP - NO ANSWER                                   	0000079664I
4293312	1	11/17/2016	PCNA	TW1 	16120	CO/APP - NO ANSWER                                	0000079664G
4293312	1	11/17/2016	PHLM	TW1 	16132	CO/APP - FEMALE SD APP WNT TO WRK WLL GVE MSG     	0000079664E

Open in new window

0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
Unfortunately, sql server does not have a listagg function like Oracle which would simplify your task. However, it can be emulated using stuff and for_xml_path. There are lots of examples on the web. Just search those terms and you will likely find examples you can use.
0
 

Author Comment

by:Shalver
Comment Utility
Can anyone give me some code that will help me start this?
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 16

Accepted Solution

by:
Pawan Kumar Khowal earned 500 total points
Comment Utility
Try..

SELECT customerID  ,    _dupe_num    ,  ActivityDate   ,   ActivityCode  ,    Collector  ,    ActivityTime 
            , STUFF 
                ((
                SELECT ', ' + a.ActivityNotes
                FROM yourtableName a
                WHERE (         a.customerID = b.customerID 
							AND a.ActivityDate = b.ActivityDate 
							AND a.ActivityCode = b.ActivityCode 
							AND a.ActivityTime =  b.ActivityTime
						)
                FOR XML PATH('')
                ) ,1,2,'') 
                AS cusr
FROM YourTableName b

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
This is just an addition to the answer provided immediately above:

It is important  apply an ORDER BY in the way the strings get concatenated. If this is not done then the meaning of the messages could get jumbled and lead to problems. The [SeqNo] column appears to be for that purpose. e.g. the pieces of the following need to be in a particular order

APP - QA-VI/ALL MM ADVSD 27 DPD $312.20 TAD APP SD, CO/APP WLL BE MKNG PYMNT ON PAY DAY AND THINKS HE, GETS PAID TMRRW, I ADVSD TO CLL CO/APP WHN HE TKES, A BREAK AND HVE HIM CLL BEFORE TMRRW AND HE SD CO/, APP WLL CLL WHN HE GETS HIS CHECK AND CASHES IT, I, ADVSD ANTHR DUE DATE AND CAN NOT CNTNUE TO CARRY, LOAN THIS WAY, HE STARTED USING FOUL LANGUAGE AND, SD WE'LL GET OUR MONEY AND HUNG UP

+---+---------+---------------------+--------------+-----------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|   |   cus   |    ActivityDate     | ActivityCode | Collector | ActivityTime |                                                                                                                                                                                                    cusr                                                                                                                                                                                                    |
+---+---------+---------------------+--------------+-----------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 4293312 | 17.11.2016 00:00:00 | COMM         | TW1       |        16261 | TCPA GIVEN ON 11/04/16                                                                                                                                                                                                                                                                                                                                                                                     |
| 2 | 4293312 | 17.11.2016 00:00:00 | PBCC         | TW1       |        16252 | APP - QA-VI/ALL MM ADVSD 27 DPD $312.20 TAD APP SD, CO/APP WLL BE MKNG PYMNT ON PAY DAY AND THINKS HE, GETS PAID TMRRW, I ADVSD TO CLL CO/APP WHN HE TKES, A BREAK AND HVE HIM CLL BEFORE TMRRW AND HE SD CO/, APP WLL CLL WHN HE GETS HIS CHECK AND CASHES IT, I, ADVSD ANTHR DUE DATE AND CAN NOT CNTNUE TO CARRY, LOAN THIS WAY, HE STARTED USING FOUL LANGUAGE AND, SD WE'LL GET OUR MONEY AND HUNG UP |
| 3 | 4293312 | 17.11.2016 00:00:00 | PCNA         | TW1       |        16091 | APP - NO ANSWER                                                                                                                                                                                                                                                                                                                                                                                            |
| 4 | 4293312 | 17.11.2016 00:00:00 | PCNA         | TW1       |        16120 | CO/APP - NO ANSWER                                                                                                                                                                                                                                                                                                                                                                                         |
| 5 | 4293312 | 17.11.2016 00:00:00 | PHLM         | TW1       |        16132 | CO/APP - FEMALE SD APP WNT TO WRK WLL GVE MSG                                                                                                                                                                                                                                                                                                                                                              |
+---+---------+---------------------+--------------+-----------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Open in new window

Also please note that you no longer need [ _dupe_num ] in the output. I have used "select distinct" in the query below to arrive at the result you see above.
SELECT DISTINCT
      cus
    , ActivityDate
    , ActivityCode
    , Collector
    , ActivityTime
    , STUFF
      ((
            SELECT
                  ', ' + a.ActivityNotes
            FROM table1 a
            WHERE (a.cus = b.cus
                  AND a.ActivityDate = b.ActivityDate
                  AND a.ActivityCode = b.ActivityCode
                  AND a.ActivityTime = b.ActivityTime
                  )
            ORDER BY
                  SeqNo DESC
            FOR xml PATH ('')
      )
      , 1, 2, '')
      AS cusr
FROM table1 b

Open in new window

It would probably be more efficient to use  cross apply but as I don't have the source tables or query "select distinct" was the best I could do with the sample data provided.
CREATE TABLE Table1
    ([Cus] int, [_dupe_num] int, [ActivityDate] datetime, [ActivityCode] varchar(4), [Collector] varchar(3), [ActivityTime] int, [ActivityNotes] varchar(50), [SeqNo] varchar(11))
;
    
INSERT INTO Table1
    ([Cus], [_dupe_num], [ActivityDate], [ActivityCode], [Collector], [ActivityTime], [ActivityNotes], [SeqNo])
VALUES
    (4293312, 1, '2016-11-17 00:00:00', 'COMM', 'TW1', 16261, 'TCPA GIVEN ON 11/04/16', '0000079663D'),
    (4293312, 1, '2016-11-17 00:00:00', 'PBCC', 'TW1', 16252, 'APP - QA-VI/ALL MM ADVSD 27 DPD $312.20 TAD APP SD', '0000079663F'),
    (4293312, 2, '2016-11-17 00:00:00', 'PBCC', 'TW1', 16252, 'CO/APP WLL BE MKNG PYMNT ON PAY DAY AND THINKS HE', '0000079663G'),
    (4293312, 3, '2016-11-17 00:00:00', 'PBCC', 'TW1', 16252, 'GETS PAID TMRRW, I ADVSD TO CLL CO/APP WHN HE TKES', '0000079663H'),
    (4293312, 4, '2016-11-17 00:00:00', 'PBCC', 'TW1', 16252, 'A BREAK AND HVE HIM CLL BEFORE TMRRW AND HE SD CO/', '0000079663I'),
    (4293312, 5, '2016-11-17 00:00:00', 'PBCC', 'TW1', 16252, 'APP WLL CLL WHN HE GETS HIS CHECK AND CASHES IT, I', '0000079664{'),
    (4293312, 6, '2016-11-17 00:00:00', 'PBCC', 'TW1', 16252, 'ADVSD ANTHR DUE DATE AND CAN NOT CNTNUE TO CARRY', '0000079664A'),
    (4293312, 7, '2016-11-17 00:00:00', 'PBCC', 'TW1', 16252, 'LOAN THIS WAY, HE STARTED USING FOUL LANGUAGE AND', '0000079664B'),
    (4293312, 8, '2016-11-17 00:00:00', 'PBCC', 'TW1', 16252, 'SD WE''LL GET OUR MONEY AND HUNG UP', '0000079664C'),
    (4293312, 1, '2016-11-17 00:00:00', 'PCNA', 'TW1', 16091, 'APP - NO ANSWER', '0000079664I'),
    (4293312, 1, '2016-11-17 00:00:00', 'PCNA', 'TW1', 16120, 'CO/APP - NO ANSWER', '0000079664G'),
    (4293312, 1, '2016-11-17 00:00:00', 'PHLM', 'TW1', 16132, 'CO/APP - FEMALE SD APP WNT TO WRK WLL GVE MSG', '0000079664E')
;

Open in new window




http://rextester.com/VSVZ88092
0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Ohh yes my bad. Missed Group by. Also changed column name from cusr to ActivityNotes. Order By- I am sure what kind of column is there- so I will leave this to the author to decide. If Int then use Order By. << Order By InnerTable that is a.SeqNo >>

Thank Paul !!.

SELECT customerID  ,    _dupe_num    ,  ActivityDate   ,   ActivityCode  ,    Collector  ,    ActivityTime 
            , STUFF 
                ((
                SELECT ', ' + a.ActivityNotes
                FROM yourtableName a
                WHERE (         a.customerID = b.customerID 
							AND a.ActivityDate = b.ActivityDate 
							AND a.ActivityCode = b.ActivityCode 
							AND a.ActivityTime =  b.ActivityTime
						) 
                FOR XML PATH('')
                ) ,1,2,'') 
                AS ActivityNotes
FROM YourTableName b
GROUP BY customerID  ,    _dupe_num    ,  ActivityDate   ,   ActivityCode  ,    Collector  ,    ActivityTime 

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
But Pawan, if you include _dup_num in the output/grouping you don't reduce the number of rows.

Try that url I provided, put _dup_num into the select list and run it again.  ( http://rextester.com/VSVZ88092 )
0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Oh,, yes need some more modification reqd... He also needs dupe_num in the Output. Updated query..

SELECT k.cus , m._dupe_num , k.ActivityDate   ,   k.ActivityCode  ,    k.Collector  ,    k.ActivityTime , k.ActivityNotes  FROM 
(
	SELECT cus, ActivityDate   ,   ActivityCode  ,    Collector  ,    ActivityTime 
				, STUFF 
					((
					SELECT ', ' + a.ActivityNotes
					FROM YourTable a
					WHERE (         a.cus = b.cus
								AND a.ActivityDate = b.ActivityDate 
								AND a.ActivityCode = b.ActivityCode 
								AND a.ActivityTime =  b.ActivityTime
							) 
					FOR XML PATH('')
					) ,1,2,'') 
					AS ActivityNotes
	FROM YourTable b
	GROUP BY cus  ,  ActivityDate   ,   ActivityCode  ,    Collector  ,    ActivityTime 
)k
CROSS APPLY
(
	SELECT TOP 1 c._dupe_num FROM YourTable c
	WHERE c.cus = k.cus AND c.ActivityDate = k.ActivityDate AND c.ActivityCode = k.ActivityCode AND
	c.Collector= k.Collector AND c.ActivityTime = k.ActivityTime
	ORDER BY SeqNo
)m

Open in new window


Output

cus         _dupe_num   ActivityDate            ActivityCode Collector ActivityTime ActivityNotes
----------- ----------- ----------------------- ------------ --------- ------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4293312     1           2016-11-17 00:00:00.000 COMM         TW1       16261        TCPA GIVEN ON 11/04/16
4293312     1           2016-11-17 00:00:00.000 PBCC         TW1       16252        APP - QA-VI/ALL MM ADVSD 27 DPD $312.20 TAD APP SD, CO/APP WLL BE MKNG PYMNT ON PAY DAY AND THINKS HE, GETS PAID TMRRW, I ADVSD TO CLL CO/APP WHN HE TKES, A BREAK AND HVE HIM CLL BEFORE TMRRW AND HE SD CO/, APP WLL CLL WHN HE GETS HIS CHECK AND CASHES IT, 
4293312     1           2016-11-17 00:00:00.000 PCNA         TW1       16091        APP - NO ANSWER
4293312     1           2016-11-17 00:00:00.000 PCNA         TW1       16120        CO/APP - NO ANSWER
4293312     1           2016-11-17 00:00:00.000 PHLM         TW1       16132        CO/APP - FEMALE SD APP WNT TO WRK WLL GVE MSG

(5 row(s) affected)

Open in new window


Hope it helps !
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
sigh

no order by in the "for xml path"

I wrote code to get the duplicate number which tells you how many rows one comment should consist of.

>>"He also needs dupe_num in the Output"
Really? I would recommend against it because the purpose is now redundant.

You could just put out a constant 1 instead? (as there is just one long comment)
The column has no value once the comments are concatenated (re-read the question)
0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Good one Sir, We can directly use constant or may be MIN()...

 --Approach 1  

--

SELECT cus , 1 _dupe_num, ActivityDate   ,   ActivityCode  ,    Collector  ,    ActivityTime 
			, STUFF 
				((
				SELECT ', ' + a.ActivityNotes
				FROM YourTable a
				WHERE (         a.cus = b.cus
							AND a.ActivityDate = b.ActivityDate 
							AND a.ActivityCode = b.ActivityCode 
							AND a.ActivityTime =  b.ActivityTime
						)  ORDER BY a.SeqNo DESC
				FOR XML PATH('')
				) ,1,2,'') 
				AS ActivityNotes
FROM YourTable b
GROUP BY cus  ,  ActivityDate   ,   ActivityCode  ,    Collector  ,    ActivityTime 

--

Open in new window



 --Approach 2  

--

SELECT cus , MIN(b._dupe_num) _dupe_num , ActivityDate   ,   ActivityCode  ,    Collector  ,    ActivityTime 
			, STUFF 
				((
				SELECT ', ' + a.ActivityNotes
				FROM YourTable a
				WHERE (         a.cus = b.cus
							AND a.ActivityDate = b.ActivityDate 
							AND a.ActivityCode = b.ActivityCode 
							AND a.ActivityTime =  b.ActivityTime
						) ORDER BY a.SeqNo DESC
				FOR XML PATH('')
				) ,1,2,'') 
				AS ActivityNotes
FROM YourTable b
GROUP BY cus  ,  ActivityDate   ,   ActivityCode  ,    Collector  ,    ActivityTime 

--

Open in new window


Output
-----------

--

cus         _dupe_num   ActivityDate            ActivityCode Collector ActivityTime ActivityNotes
----------- ----------- ----------------------- ------------ --------- ------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4293312     1           2016-11-17 00:00:00.000 COMM         TW1       16261        TCPA GIVEN ON 11/04/16
4293312     1           2016-11-17 00:00:00.000 PBCC         TW1       16252        APP - QA-VI/ALL MM ADVSD 27 DPD $312.20 TAD APP SD, CO/APP WLL BE MKNG PYMNT ON PAY DAY AND THINKS HE, GETS PAID TMRRW, I ADVSD TO CLL CO/APP WHN HE TKES, A BREAK AND HVE HIM CLL BEFORE TMRRW AND HE SD CO/, APP WLL CLL WHN HE GETS HIS CHECK AND CASHES IT, 
4293312     1           2016-11-17 00:00:00.000 PCNA         TW1       16091        APP - NO ANSWER
4293312     1           2016-11-17 00:00:00.000 PCNA         TW1       16120        CO/APP - NO ANSWER
4293312     1           2016-11-17 00:00:00.000 PHLM         TW1       16132        CO/APP - FEMALE SD APP WNT TO WRK WLL GVE MSG

(5 row(s) affected)

Open in new window



Hope it helps !!
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
Pawan Kumar Khowal,
Thank you for providing the for xml path solution I suggested. I figured that was what was needed and you demonstrated it nicely.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now