Solved

Concatenating multiple comments into one row

Posted on 2016-11-18
16
79 Views
Last Modified: 2016-12-28
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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +2
16 Comments
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41893124
sample code please, attachment missing?
0
 

Author Comment

by:Shalver
ID: 41893127
0
 

Author Comment

by:Shalver
ID: 41893129
I think I have attached the file ..
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 66

Expert Comment

by:Jim Horn
ID: 41893145
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
ID: 41893155
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 32

Expert Comment

by:awking00
ID: 41893486
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
ID: 41893754
Can anyone give me some code that will help me start this?
0
 
LVL 29

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41893870
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 49

Expert Comment

by:PortletPaul
ID: 41893909
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 29

Expert Comment

by:Pawan Kumar
ID: 41893912
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 49

Expert Comment

by:PortletPaul
ID: 41893916
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 29

Expert Comment

by:Pawan Kumar
ID: 41893934
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 49

Expert Comment

by:PortletPaul
ID: 41894008
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 29

Expert Comment

by:Pawan Kumar
ID: 41894099
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 32

Expert Comment

by:awking00
ID: 41909228
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

 Watch the Recording: Learning MySQL 5.7

MySQL 5.7 has a lot of new features. If you've dabbled with an older version of MySQL, it is definitely worth learning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

635 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