Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 88
  • Last Modified:

Concatenating multiple comments into one row

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
Shalver
Asked:
Shalver
  • 5
  • 4
  • 3
  • +2
1 Solution
 
Pawan KumarDatabase ExpertCommented:
sample code please, attachment missing?
0
 
ShalverAuthor Commented:
0
 
ShalverAuthor Commented:
I think I have attached the file ..
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
ShalverAuthor Commented:
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
 
awking00Commented:
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
 
ShalverAuthor Commented:
Can anyone give me some code that will help me start this?
0
 
Pawan KumarDatabase ExpertCommented:
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
 
PortletPaulCommented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
PortletPaulCommented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
PortletPaulCommented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
awking00Commented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now